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.