How to calculate number of months between two dates when one date falls outside of model time range
I am needing to calculate the number of months between two dates, one of the dates falls outside of the model time range. I am needing this so I can do a straight line depreciation cacluation but the # of months formula returns a blank value because of the date/time range isue.
Answers
-
I would create a time range in the time settings that included all periods you are trying to calculate from and to. This will add those time periods to the superset of time in the model and make them appear in your calculation as valid time periods. You do not need to use this time range in ANY module or line item for it to work.
1 -
Out of curiosity, what logic are you using?
Using lines with a format of 'Time Period' will likely fail because Anaplan doesn't show time periods that fall off the timescale. You can definitely use a separate longer time range to get around this, but I'd be curious what's stopping you from using lines with a 'Date' format, as these don't have the same.. restriction.
There's a number of options in the below thread that may help,
0 -
@wld002 Glad to hear you got a working solution.
I would still recommend trying out one of the date options to see if it gets you the same outcome, without the need for the time range.
Whilst the time range works, it does introduce some maintenance to roll it each FY and may lead to some confusion in the model as even though your time range isn't published everywhere, those periods will now be available as selections in all modules where time period is available. Doesn't break anything, but may be a quality of live consideration.
0 -
It shouldn't be necessary the Time ranges… as in the link suggested by @luke_e
For nr. of months:
(Year(EndDate)*12 + Month(EndDate)) - (Year(StartDate)*12 + Month(StartDate)) + 1
Can be added +1, if it's needed…
in the picture, however, it was showing 365 ( nr. of days) that can be calculated as the difference between the dates…
1
