Calculation Formulas
Back to Calculation Formulas Overview
Back to Functions Overview
Function "CalendarAdd"
CalendarAdd(datevalue, timezone, value, unit)
Function: Performs calendar arithmetic on the given timestamp and returns a new, modified timestamp. The timestamp given in the "datevalue" argument (represented in milliseconds since "Jan. 1st 1970, 00:00:00 GMT") is interpreted as a date/time in the given "timezone". To this date/time, the given number of days, weeks, months or years is then added, and the resulting date/time value is again returned as a timestamp value (again represented in milliseconds since "Jan. 1st 1970, 00:00:00 GMT").
Note that adding a day, week, month or year can not be broken down into a simple "add a certain number of hours (or minutes/seconds/milliseconds)" formula. Instead, the calendar arithmetic works as follows:
-
Adding one day to a certain timestamp means that after the add, the modified timestamp represents the exact same time-of-the-day of the following day. I.e. if the original timestamp was 9:15h on Dec 7th, 2011, then the new timestamp will be 9:15h on Dec 8th, 2011. This is true on normal days (where the two timestamps have a difference of exactly 24 hours) but also if the two days should straddle the change between normal time and daylight saving time in spring (when the difference is only 23 hours) or the change between daylight saving time and normal time in autumn (when the difference is 25 hours). If in these cases you would simply add 24 hours to the original timestamp, then the resulting timestamp would be off by 1 hour. The CalendarAdd function takes this into account, according to the daylight saving time rules associated with the given timezone.
-
Adding one week to a certain timestamp means that after the add, the modified timestamp represents the exact same time-of-the-day of the exact same day-of-the-week of the following week. I.e. if the original timestamp was 10:22h on the Wednesday of week 10, then the new timestamp will be 10:22h on the Wednesday of week 11. This is not a simple case of adding 7*24 hours to the timestamp because the difference could straddle the daylight saving time change, which would cause the result to be off by 1 hour. The CalendarAdd function takes this into account, just like when adding days.
-
Adding one month to a certain timestamp means that after the add, the modified timestamp represents the exact same time-of-the-day of the exact same day-of-the-month of the following month. I.e. if the original timestamp was 8:10h on July 15th, then the new timestamp will be 8:10h on August 15th. This is not a simple case of adding N*24 hours to the timestamp (with N being the number of days in a month) because different months have a different number of days (plus the complications added by leap years and daylight saving time rules).
The CalendarAdd function takes this into account so that the result is the same day-of-the-month in the new month, if possible. In some cases, the matching day-of-the-month however does not exist in the new month. For example, April, June, September and November have no 31st day, and February usually has no 29th, 30th and 31st day (except in leap years, where it has a 29th day). So if a month-add operation would result in a day that does not exist in the calculated target month, the CalendarAdd function defaults to the last day in the target month. For example, if the original timestamp was for March 31st, then adding 1 month will not yield April 31st (because April only has 30 days), but will instead yield April 30th. Or if the original timestamp was for Febuary 29th, 2004, then adding 12 months will not yield February 29th, 2005 (because 2005 is no leap year, therefore it does not have a Feb 29th), but will instead yield February 28th, 2005.
When adding months, CalendarAdd will also correctly observe the daylight saving time rules for the given timezone.
-
Adding one year to a certain timestamp means that after the add, the modified timestamp represents the exact same time-of-the-day of the exact same day-of-the-month of the exact same month-of-the-year of the following year. I.e. if the original timestamp was 6:30h on May 10th, 2010, then the new timestamp will be 6:30h on May 10th, 2011. This is not a simple case of adding N*24 hours to the timestamp (with N being the number of days in a year) because leap years have 1 more day than normal years (plus the complications added by daylight saving time rules).
The CalendarAdd function takes this into account so that the result is the same day-of-the-month in the same month-of-the-year, if possible. There is one case in which this is not possible: If the original timestamp specifies Feburary 29th in a leap year, but the new timestamp is not in a leap year, so there is no Feburary 29th. In this case, the new timestamp will be for Feburary 28th of the target year.
When adding years, CalendarAdd will also correctly observe the daylight saving time rules for the given timezone.
Of course it is also possible to add several days/months/weeks/years by specifying a value other than "1", or to specify a negative value to move into the past, instead of into the future.
Return-Type: Number
Arguments:
datevalue - Type Number: The original timestamp, as milliseconds since
"Jan. 1st 1970, 00:00:00 GMT".
timezone - Type Text: The ID or value of the timezone that shall be used to
interpret the timestamp as a date/time value. See here
for possible timezone values.
value - Type Number: The amount that shall be added to the date/time value. This
is combined with the "unit" to determine, how many days, weeks, months or years shall be added to the date/time value. Can
be positive (move the date/time into the future) or negative (move the date/time into the past).
unit - Type Text: The unit for the amount that shall be added to the date/time
value. The only allowed values are "days", "weeks",
"months" and "years" (with this exact spelling, all
lower-case).
Examples:
CalendarAdd(CurrentMillis, "PST", 1, "years")
(if executed at a certain time on the 1st of April of a certain year, it returns a milliseconds value that represents the exact same time on the 1st of April in the following year)
CalendarAdd(CurrentMillis, "PST", -4, "weeks")
(if executed at a certain time on a Tuesday of a certain week, it returns a milliseconds value that represents the exact same time on the Tuesday that was four weeks earlier)
CalendarAdd(CurrentMillis, "EST", 10, "months")
(if executed at a certain time on the 7th of a certain month, it returns a milliseconds value that represents the exact same time on the 7th in the month that is 10 months later)
CalendarAdd(CurrentMillis, "CET", -1, "days")
(if executed at a certain time, it returns a milliseconds value that represents the same time on the previous day - on most days of the year that is exactly 24 hours before the current time, but if executed on the last Sunday in March [begin of daylight saving time in CET], the difference is only 23 hours, and if executed on the last Sunday in October [end of daylight saving time in CET], the differences is 25 hours)
CalendarAdd(CurrentMillis, "GMT", -1, "days")
(if executed at a certain time, it returns a milliseconds value that represents the date/time that is exactly 24 hours before the current time - it's always exactly 24 hours because GMT does not have daylight saving time)