When working with Adaptive Planning, especially for the first time as I have recently done, it is a little difficult understanding the time functions and what they return when they are used within time-series sheets (such as the Standard, Cube or Calculated Accounts in Modelled Sheets).
If you’re looking for a brief understanding on what each of these functions return here’s a little sample on what they return. Assumptions about the structure of the data are as follows:
- January 2013 is the beginning of the version.
- January 2014 is the beginning of the plan.
- The fiscal year of the budget is the same as the calendar year – i.e. Jan-Dec.
| Type | Jan-13 (1st month in version) | Feb-13 (2nd month ) | Jan-14 (1st month in budget/plan ) |
|---|---|---|---|
Day(this) | 15 | 15 | 15 |
DaysInMonth(this) | 31 | 28 | 31 |
FiscalMonth(this) | 1 | 2 | 1 |
FiscalQuarter(this) | 1 | 1 | 1 |
FiscalYear(this) | 2013 | 2013 | 2014 |
Month(this) | 1 | 2 | 1 |
Quarter(this) | 1 | 1 | 1 |
ToDate(Year(this), Month(this)) | ERR | ERR | ERR |
ToDate(Year(this), Month(this), Day(this)) | ERR | ERR | ERR |
VersionMonth(this) | 0 | 1 | 12 |
VersionYear(this) | 0 | 0 | 1 |
Year(this) | 2013 | 2013 | 2014 |
What does ERR mean?
ERR– The reason why theToDatefunctions return an error is because they return a date value whereas the cell only handles numbers (or percentages). In the example above theToDate( Year( this ), Month( this ), Day( this ) )function would return the following results for each column:- Jan-13 = 2013/01/15
- Feb-13 = 2013/02/15
- Jan-14 = 2014/01/15
However, as the return types can only be of type number or percentage, this return type instead returns ERR.