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

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.

Ryan

Author of scripteverything.com, Ryan has been dabbling in code since the late '90s when he cut his teeth by exploring VBA in Excel when trying to do something more. 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. When he is not behind a screen, Ryan enjoys a good bush walk with the family during the cooler months, and going with them to the beach during the warmer months.

Recent Posts