Skip to Content

Working With Date Functions In Adaptive Planning

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.
TypeJan-13 (1st month in version)Feb-13 (2nd month )Jan-14 (1st month in budget/plan )
Day(this)151515
DaysInMonth(this)312831
FiscalMonth(this)121
FiscalQuarter(this)111
FiscalYear(this)201320132014
Month(this)121
Quarter(this)111
ToDate(Year(this), Month(this))ERRERRERR
ToDate(Year(this), Month(this), Day(this))ERRERRERR
VersionMonth(this)0112
VersionYear(this)001
Year(this)201320132014
React JS calendar with date selector
React JS calendar with date selector

What does ERR mean?

  • ERR – The reason why the ToDate functions return an error is because they return a date value whereas the cell only handles numbers (or percentages). In the example above the ToDate( 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.