Get Number Of Periods Between Dates In Adaptive Planning


If within a Modelled Sheet you have a date field and need to calculate how many periods the row has been active for, then you can create a formula to capture this.

Example

If we assume we have an input account on our Modelled Sheet labelled StartDate then our formula for determining how many periods have happened since StartDate would be as follows:

( ( year( this ) - year( ROW.StartDate ) ) * 12 ) + ( month( this ) - month( ROW.StartDate ) )

This assumes the time stratum of the sheet is monthly.

What if I don’t want negative periods in the result?

This formula will also insert negative values for months before the StartDate value. If you’d like to return a zero value for periods prior to the StartDate then you might want to add to your formula an if condition:

if ( this >= ROW.StartDate, ( ( year( this ) - year( ROW.StartDate ) ) * 12 ) + ( month( this ) - month( ROW.StartDate ) ), 0)

What if I have an end date field?

You could extend this example eve further by checking if the current period is before an end date input value too. We’ll assume the code name is EndDate:

if ( this >= ROW.StartDate, if (this <= ROW.EndDate, ( ( year( this ) - year( ROW.StartDate ) ) * 12 ) + ( month( this ) - month( ROW.StartDate ) ), 0), 0)

What if the end date is an optional field?

Here we would need to extend our formula by checking if the EndDate field has a value:

if ( this >= ROW.StartDate, 
  if (ISBLANK(ROW.EndDate) OR this <= ROW.EndDate, 
    ( ( year( this ) - year( ROW.StartDate ) ) * 12 ) + ( month( this ) - month( ROW.StartDate ) )
  , 0 )
, 0 )

As the logic for this type of formula can become difficult to manage as additional complexities come into the model what I tend to do is create a “flag” account that determines whether something is active.

Therefore, I move the logic to determine if something is active to a new Modeled Account and label this IsActive, it then would have the following logic:

if (this >= ROW.StartDate,
    if (ISBLANK(Row.EndDate), 1,
        if (this <= ROW.EndDate, 1, 0)
    )
, 0 )

Then in my period formula I would change this to now be:

if (ROW.IsActive = 1,
    ( ( year( this ) - year( ROW.StartDate ) ) * 12 ) + ( month( this ) - month( ROW.StartDate ) )
, 0)

It’s a lot easier to read and much easier for other developers to understand (including yourself when you come back to your code months and years later!).

Conclusion

Calculating the number of periods between dates in a Modeled Sheet can help with formulas needing this calculation such as loans, leases and employee/payroll formulas.

Look at separating out your logic when you develop your formulas and try to keep them small.

Recent Posts