DateDifference

The DateDifference() formula (valid for relational tables as well as date-type values in Cloud EPM Platform and Essbase grids) calculates the difference between to referenced date values. The difference is in terms of the calendar-unit specified days, months or years.

Syntax

DateDifference(referenceA, referenceB, calendar-unit)

DateDiff(referenceA, referenceB, calendar-unit)

Parameters

referenceA - This is the start date. The reference can be either a column reference (eg: [C], [C(E)]) or a cell reference (eg: [1,C], [1(5), C], [1(5), C(E)]. If a column reference (eg: [C], [C(E)]) the row is the current row.

referenceB - This is the 'end date'. The reference can be either a column reference (eg: [C], [C(E)]) or a cell reference (eg: [1,C], [1(5), C], [1(5), C(E)]. If a column reference (eg: [C], [C(E)]) the row is the current row.

calendar-unit - The calendar unit is a required parameter. It can be either: day, month, or year; and will determine in what unit of measurement the data difference is calculated. A 0, 1, or 2 can also be used in place of day, month, or year, respectively.

Note:

If the start-date occurs after the end-date, the difference is negative, or the difference is positive.

If either referenceA or referenceB points to a non-date value, an #ERROR is returned.

There is no additional validation done when a DateDifference() function references a column/cell which does not contain a date. The function just evaluates to #ERROR.

The internal representation of dates is in terms of 'milliseconds since 1/1/1970'. So, the internal representation has the ability to include details to the millisecond. So, the number of days between two dates also takes into account the time of day to the millisecond, and so 7/20/2023 19:00 and 7/22/2023 07:00 is 1 day (not 2), because it takes into account the number of full-24hour-days. When the date is displayed as a cell value, it may (or may not) show the hours:minutes:seconds:milliseconds, but the internal representation is what is used, not the visible, formatted date during the DateDifference() calculations.

Example

If the date value in the referenced column B is 'Jul, 21, 2023' and the date value in the referenced column C is 'Aug, 25, 2023':
  • DateDiff([B], [C], day) will return 35
  • DateDiff([C], [B], day) will return -35
  • DateDiff([B], [C], month) will return 1
  • DateDiff([C], [B], month) will return -1
  • DateDiff([B], [C], year) will return 0

Example 2

If the date value in the referenced column B is 'Jul, 21, 2023' and the date value in the referenced column C is 'Aug, 25, 2024':
  • DateDiff([B], [C], day) will return 401
  • DateDiff([B], [C], month) will return 13
  • DateDiff([C], [B], month) will return -13
  • DateDiff([B], [C], year) will return 1
  • DateDiff([C], [B], year) will return -1