Calculate the Difference Between Two Dates (Days/Months)

Calculating the difference between two dates is a common requirement and one that you will likely need to do at some point. Below you will find the best practice for calculating the difference between two days, and the best practice for calculating the difference between two months. 

While there are multiple ways to get to the answer you're looking for, often the simplest solution is the best. When calculating the difference between two dates (days or months), use this approach first: 

For date formatted line items, use: line item 1 – line item 2.

For time formatted line items, use: START(line item 1) – START(line item 2).

Dates

1. Simple dates difference.
DS1.png

2. Dates addition.
DS2.png

3. Dates subtraction.

DS3.png

Time Periods

1. Time period difference.
A.png

2. Months addition.
B.png

3. Months subtraction.
C.png

4. Years addition.
D.png

5. Years subtraction.
E.png

Keep 'Superset' in Mind When Selecting Your Time Period

If you choose a time period that is outside of the superset, it will display as blank. The superset is the earliest date from the model calendar and any time range through to the later of the last date of the calendar of any time range. 

Example model:
AA.png

Time:
BB.png

Superset = Jan 17 - Dec 22:

DD.pngCC.png

If you go outside of that range:
EE.png 

You're now ready to calculate date differences in your daily practice! If you have additional questions or feedback, please add them to the comments below. 

The content in this article has not been evaluated for all Anaplan implementations and may not be recommended for your specific situation.
Please consult your internal administrators prior to applying any of the ideas or steps in this article.
Comments

Helpful post as always, David.  One use case not covered here is calculating the difference in months between 2 monthly periods.  I have generally solved this with a formula:

 

Mitch_Max_0-1576752859209.png

Thank you David! Sometimes I forget how easy is to use substract and add with time. 

Sometimes we also need to go outside of the defined time period and include those time ranges in our calculation.

In this case, what would you recommend to do? 

Great article, @DavidSmith  - simple and effective.

Also, does the kudos button for the article appear in a strange place for anyone else?

@CallumW - you mean under the "labels" box? Weird!

@emilydunn - worth having a look at why "kudos" doesn't appear directly under the article but off to the side or is it meant to be like that?

andrewtye_0-1578064831188.png

 

@andrewtye- precisely!

I've noticed kudos buttons dotted around in weird areas sometimes, but this one looked particularly odd!

@andrewtye You are correct about kudos.  In a future update we'll be resolving this so that it is in the same spot for both Blogs and Knowledge Base articles.

Great post David. One workaround I've used to find the difference in months between 2 time periods (to @Mitch_Max point)  is creating a sequential order against the time dimension using the formula below, and then just leveraging that in calculations through out the model. 

Austinv_1-1592238468484.png

 

Then once that is in place you can look up the difference between the periods using a simple lookup. This can be replicated at the week, month, quarter, year levels as well. 

Austinv_2-1592238712581.png

 

That's correct @Austinv but assumes that both periods are within the Time Range in Time Module.  This may not always be the case -  for example, when computing the length of time of a contract or relationship where the start time precedes the Time Range, or extends afterwards.

 

Mitch

Contributors
Latest Articles
a week ago
User Provisioning
Best Practices
2 weeks ago
by Misbah
Transactional API Tutor
Best Practices
2 weeks ago
Labels (2)