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.
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 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 .

Photo of author
Ryan Sheehy
Ryan has been dabbling in code since the late '90s when he cut his teeth exploring VBA in Excel. Having his eyes opened with the potential of automating repetitive tasks, he expanded to Python and then moved over to scripting languages such as HTML, CSS, Javascript and PHP.