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.

Accepted answers

All comments

jasonblinn

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.

luke_e

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,

https://community.anaplan.com/discussion/849/how-to-calculate-months-difference-between-two-dates

wld002

@luke_e Jason's solution worked. Creating a time range that was 7 years but not applied allowed for the calculation of # of months between two dates.

luke_e

@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.

alexpavel

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…

Quick Links

Can you help?

Unanswered questions

Stuck in Activity 2 of L1 exaam
Not able toidentify error. And what is this builtin. Kindly guide
Azure blob connection Cloudworks vs ADO
I am trying to connect to Azure blob, the credentials work on cloudworks, but do not work on ADO. Has anyone else experienced this issue?
Berapa nomor Tokopedia?
Tokopedia Menyediakan layanan WhatsApp? Untuk menghubungi layanan Tokopedia melalui (WA)62_08174_9080_16 kamu bisa mengikuti.