Date Calculations with AddUp 2

Dates can be manipulated in many ways once we have a proper notation format to represent them. AddUp uses a "timestamp" notation: a date timestamp is written using a year value, a month value and a day value separated from each other by dashes and placed inside square brackets. For example: [2000-12-31] is a date timestamp that represents December 31st of the year two thousand. Notice how the most-significant number in a date (the year) comes first, followed by the month and then the day.

What exactly is a "timestamp"? It is an expression that AddUp translates into a number of seconds elapsed since January 1st of year 0. The square brackets indicate that the content is not a subtraction or any other operation but that it represents a date and/or a time. The value of this timestamp expression is just a plain number, a number of seconds in this case. For example, the above timestamp has this value:

[2000-12-31] = 63,145,440,000

Since timestamps represent a number of seconds and since a second is the standard unit of time, we can use date values in all calculations where a unit of time is suitable. For example, we can do a date difference calculation by subtracting two dates to see how much time has passed between them.

[2006-06-30] - [2000-12-31] = 173,404,800

Of course the number of seconds may not be what we want to know, so we convert this value into days, weeks, months or years (or any other time unit we need). Apply the "->" conversion operator to the result of the subtraction and select the desired time unit. Be sure to use parentheses around the subtraction so that the conversion applies to all of it and not just to the last date value.

Difference between two dates in years.

([2006-06-30] - [2000-12-31]) -> years = 5.495

Difference between two dates in months.

([2006-06-30] - [2000-12-31]) -> months = 65.94

Difference between two dates in weeks.

([2006-06-30] - [2000-12-31]) -> weeks = 286.714

Difference between two dates in days.

([2006-06-30] - [2000-12-31]) -> days = 2,007

Normally, the later date is subtracted from the earlier date to get a positive value. If the reverse is done instead then the value will be negative but the amount of time is still correct in absolute terms.

Another kind of date calculation is to add or subtract some amount of time from a known date in order to obtain a different date. This is also easily done with an expression such as this:

[2000-12-31] + 5 days = 63,145,872,000

Here again, the number of seconds obtained from this operation is not very informative, it is just the timestamp's value (in seconds) plus the number of seconds in 5 days. We want to see an actual date instead. To get the result in timestamp format, start the operation with a '[ prefix. The single quote indicates that what follows is an output format specification. The left square bracket says that this format should be a date timestamp. A formatting prefix must not contain any space because it ends at the first space character found before the expression itself, but separate the prefix and the expression itself with at least one space.

Show a timestamp for 5 days later than [2000-12-31].

'[ [2000-12-31] + 5 days = [2001-01-05]

Show a timestamp for 365 days earlier than [2000-12-31].

'[ [2000-12-31] - 365 days = [2000-01-01]

Did you expect the previous example to yield [1999-12-31] instead of [2000-01-01]? Remember that 2000 was a leap year of 366 days. You can apply function leap to any date timestamp to see if it occurs within a leap year. The function returns 1 for a leap year timestamp, 0 otherwise.

leap [2000-12-31] = 1

leap [1999-12-31] = 0

Dates can be abbreviated by omitting their day and/or month value(s). Omitted values are assumed to be 1. Using abbreviations we can check for leap years more simply:

leap [2000] = 1

leap [1999] = 0

Be sure to retain the square brackets whenever date functions are used, otherwise the value will be interpreted as a plain number with unpredictable results. For example:

leap 2000 = 1

leap 1999 = 1 <-- Wrong! Not using a timestamp.

Other useful date functions include:

returns a timestamp for the current date
dow (dayOfWeek)
returns a number from 1 to 7 to indicate the day of the week (Monday to Sunday)
doy (dayOfYear)
returns a number from 1 to 366 to indicate the ordinal day of the year
getDy (getDay)
returns the day value of a date timestamp
getMo (getMonth)
returns the month value of a date timestamp
getYr (getYear)
returns the year value of a date timestamp

More formatting alternatives exist for timestamp values. Dates can be combined with times. Punctuation can be omitted or used to specify default values. Year plus day-of-year can be used instead of year, month and day to form dates. These alternative formats and other details are described in the help file that accompanies the installation program.