Skip to main content
Adaptive Insights
Knowledge and Support - Adaptive Insights

Date Functions on Calculated Model Accounts

Question

What functions can be used with the time modifier "this" or with a a Modeled Sheet date column?

Answer

The following functions can be used with the time modifier “this,” or with a Modeled Sheet date column such as ROW.StartDate. They cannot be used with a literal date string, e.g. Day(01/15/2013.) 

ToDate (Y, M, D)

  • Returns the date specified by the given year Y, month M, and day D. If D is omitted, 15 is used. The value returned may be used as an argument to other functions which take a date.
  • Example: This formula would return the number of days an employee has been employed since the employee's start date (as of the end of each month the employee is with the company).
  • = If(ROW.partialheadcount>0, todate(year(this), month(this), daysinmonth(this)) - ROW.StartDate,0)

Day (D)

  • Returns the day of the month for date D.
  • Example: This formula would return the day of the month on which an employee starts. If the start date is 07/15/2010, the formula would evaluate to 15.
  • = Day(ROW.StartDate)

Month (D)

  • Returns the month in which date D occurs.
  • Example: This formula would return the month in which an employee starts. If the start date is 07/15/2010, the formula would evaluate to 7.
  • = Month(ROW.StartDate)

Quarter (D) 

  • Returns the quarter in which date D occurs.
  • Example: This formula would apply an adjustment to the units input in the sheet in months that fall within the first or last quarter.
  • = If(Quarter(this) = 1 or Quarter(this) = 4, ACCT.Units * ASSUM.SeasonalAdjustment, ACCT.Units)

Year (D)

  • Returns the year in which date D occurs.
  • Example: This formula would user the New Assumption if the year in which the formula is being calculated is after 2010, and the Old Assumption if the year in which the formula is being calculated is 2010 or before. Then the Assumption used would be multiplied by the Salary value of the current period.
  • = Iff (Year(this) > 2010, ASSUM.New, ASSUM.Old) * ROW.Salary

FiscalMonth (D)

  • Returns the month of the fiscal year in which date D occurs.
  • Example: This formula would return the fiscal month in which an employee starts. If the fiscal year begins in July, and the employee start date is 07/15/2010, the formula would evaluate to 1.
  • = FiscalMonth(ROW.StartDate)

FiscalQuarter (D)

  • Returns the quarter of the fiscal year in which date D occurs.
  • Example: This formula would return the fiscal quarter in which an employee starts. If the fiscal year begins in July, and the employee start date is 07/15/2010, the formula would evaluate to 1.
  • = FiscalQuarter(ROW.StartDate)

FiscalYear (D)

  • Returns the fiscal year in which date D occurs
  • Example: This formula would return the Headcount for an employee if the fiscal year in which an emplyee start is before the fiscal year in which the formula exists. Otherwise, the formula would return zero. 
  • = If (FiscalYear(this) > FiscalYear(Row.StartDate), ROW.Headcount, 0)

VersionMonth (D)

  • Returns N-1 for a date D that occurs during the Nth month of the version. Can be negative if D is earlier than the start of the version.  The first month of a version is equal to "0" in terms of VersionMonth. If the version starts in January 2013, and the employee's hire date is March 2013, the VersionMonth would be "2".
  • Example: This formula would evaluate to "1" in the month in which the employee is hired.
  • = If(VersionMonth(this) = VersionMonth(ROW.StartDate), 1, 0)

VersionYear (D) 

  • Returns N-1 for a date D that occurs during the Nth year of the version. Can be negative if D is earlier than the start of the version. The first year in version is VersionYear "0". 
  • Example: This formula would evaluated to "1" for an employee with a start date of 02/05/2014 if the first year in the version is 2013.
  • = VersionYear(ROW.StartDate)

DaysInMonth (D)

  •  Returns the number of days in the month in which date D occurs.
  • Example: This formula would return the number of days in the month in which the employee was hired.
  • = DaysInMonth(ROW.StartDate)

MonthFraction (D1, D2, D)

  •  Returns the portion of the current month which occurs between the start date D1 and the end date D2. If optional date D is specified, uses the month in which D occurs instead of the current month.
  • Example: This formula would evaluate the portion of the month an employee is with the company during the start and end months of his or her employment. If the start date is 06/15/2013, the formula would evaluate to ".5" in June 2013.
  • = MonthFraction(ROW.StartDate, ROW.EndDate, this, 30)
  • Was this article helpful?