It has been a long time since I blogged about a formula. Not that I haven’t built any, just nothing really new and exciting…until now that is!

The problem under consideration is calculating the number of months between two dates. Before you go all “Phhhh, that is cake” take another think on this! See, it is easy for a human. We know that there are 12 months in a year, but to get a machine to do this automagically, well, it is not so straight forward. Getting days is easy (Date2 – Date1) and getting years even wouldn’t be too bad (year(date2)-year(date1)) but month is kind of an oddity, because you can’t just say Month(date2)-Month(date1) because the month is just this lonely little digit out there…you need the year to give context. But, how do you express that weirdness in a formula? Well, read on my friends.

My first instinct was to go with the number of days divided by 30, but about a nano second after that popped in my head, I remembered the whole “odd number of days in a month” thing. Not to mention, freaking leap years!

So, back to the drawing board I went…and I thought and I sketched some ideas out and finally, I hit upon the solution! I needed to not think about this as one problem, but three separate math problems.

To start with, here are my dates:

5/1/2015 and 6/23/2018

First thing, in the oldest date, we need to calculate the number of months left in that dates year…

Example – if the date is 5/1/2015, the value here would be 12 – 5 , which equals 7

Second, in the newest date, we need to calculate the number of months that have already passed…

Example – if the date is 6/23/2018, the value would be 6

Third, we figure out how many FULL years are between the newest year and next oldest year and we multiply that by 12. Since we have already considered the number of months for the oldest date, we need to add 1 to the year. We then subtract that number from the year value of the newest date.

Example – the next year for the oldest date is 2016. The newest year is 2018. The difference is 2 full years or (2*12) = 24.

Now, you sum all three values together like so:

7 (Number of Months Left in Oldest Date) + 6 (Number of Months that have passed in Newest Date)+(2*12(Number of Full Years times 12)) = 37 months.

Of course, the formula doesn’t actually look this nice…the formula itself is, well, awesomely gnarly!

Feel free to use, just replace “Your End Date” with whatever end date you want and “Your Start Date” with whatever start date you want!

/*checks first if the start / end dates are in the same year. If this is true, then the value should just be the month – month*/

if(year(Your_End_Date)-year(Your_Start_Date)=0,month(Your_End_date)-month(Your_Start_Date),

/*If this is not the case, are they at least 1 full calendar year apart? If this is the case, then do the big calc*/

if((year(Your_End_date)-(year(Your_Start_Date)+1))>=1,

/*Value if True*/

(

((year(Your_End_date))-(year(Your_Start_Date)+1))*12)+

((12-month(Your_Start_Date))+month(Your_End_date)),

/*If the years are not more than 1 apart, do this calc instead*/

(12-month(Your_Start_Date))+month(Your_End_date)))

An awesome variation of this is where you substitute “start date” with “today()”. This will give you a dynamic countdown of the months left between “today()” and whatever the end date is, as long as your start date is before “today”!!!

So, where would you use this? I built this out for a specific request, but I could imagine a couple other purposes:

• Depreciation of a value
• Territory Realignment that is weighted for time remaining in a date range
• Reporting on the number of months between a created date and a closed date

Any other ideas? Thoughts?

Let me know!

andrew