Categories
Cloud Apps

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.