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
SFDCinSeatown 
Thank you! I wrestled with the same issue – we need to know the number of months left until a specified end date for some salary calculations. I had come up your logic, but not being a programmer, I was having a hard time translating the logic into a formula.
Thank you for your help!
glad to be of help!
Comments like this make my day:-)
Hi Andrew contract starts in 1st Jan & end 31st Dec, showing as 11 mths not 12. Any advice on how I can correct the formula?
Hi Lucy,
if(year(Your_End_Date)-year(Your_Start_Date)=0,month(Your_End_date)-month(Your_Start_Date)
should be
if(year(Your_End_Date)-year(Your_Start_Date)=0,(month(Your_End_date)-month(Your_Start_Date))+1
Basically, since the there is a full month being accounted for on either the start month or end month, you need to account for that full month.
This could potentially lead to some interesting calcs though, for example. If you contract start date is 2/1/2016 and the end date is 12/31/2016, the formula would return the months in the contract as 11 instead of the true “Month – Month” of 10.
Let me know if you have any other questions! Comments like this make my day!
Andrew
Hi Andrew, if I switch to using the above formula you recommended to Lucy with the +1 then when I have dates such as June 18 2016 – Dec 17 2016, this is not calculating as 6 months, it thinks its 7. Is there a version of the formula that will work in both use cases and either version seems to have a situation where it won’t work? Thanks for any guidance on this.
Hey Emma,
Thanks for dropping by!
This is a great question, let me dig around a bit and see what I come up with.
Hi Again,
If you are looking to just calculate the difference between months, you can use the formula posted in the blog post as it assumes that the start month is Zero instead of 1. The formula in the comment would be for situations where you wanted that first month to count as 1, though it is a “YMMV” type of mod.
Dates in general are trickier to work with than most other them functions in Salesforce. What I would suggest doing (And do so myself!) for testing is to build a similar type of formula in excel. This way, you can quickly test that formula.
if(year(End_Date__c)-year(Start_Date__c)=0,month(End_Date__c)-month(Start_Date__c),
if((year(End_Date__c)-(year(Start_Date__c)+1))>=1,
(
((year(End_Date__c))-(year(Start_Date__c)+1))*12)+
((12-month(Start_Date__c))+month(End_Date__c)),
(12-month(Start_Date__c))+month(End_Date__c)))
Great article. Any ideas how to calculate number of working days per month in a date range.
Say the range was 01/15/2016 – 02/15/2016
So how many working days in January and how many in February?
I am guessing you would need 12 formula fields for each month but am stuck from there on in
Hey Simon!
Thanks for dropping by and for the comment!
A question on the dates…would it be safe to assume that these dates are captured on a record somewhere (CloseDate / CreateDate for example) or are they more static, like fiscal months?
You just saved me a bu**-load of time. Thanks a ton. I used this for a nonprofit to calculate the number of years or part years a grant term is based on the grant start and end date. Just added a /12 attachment on the formula. Since grants can be for whole or part years or whatever, this helped. Was there an easier way? Maybe, but I found this first and it did the trick.
Hey Michelle,
Thanks for dropping by and THANK YOU for leaving such a nice note.
Every time I hear from someone that some tip I wrote about on my blog saved them some time, it puts a big smile on my face! Couple that with you using for a Non Profit??? That is even better.
Have a good one.
Andrew
This was a huge help for me, but I need to count the first month for the multi-year scenario as well. This is what I have so far. Can you tell me where in the multi year part do I need to add in +1?
if(year(TODAY())-year(Recurring_Start_Date__c)=0,(month(TODAY())-month(Recurring_Start_Date__c))+1,
if((year(TODAY())-(year(Recurring_Start_Date__c)+1))>=1,
/*Value if True*/
(
((year(TODAY()))-(year(Recurring_Start_Date__c)+1))*12)+
((12-month(Recurring_Start_Date__c))+month(TODAY())),
(12-month(Recurring_Start_Date__c))+month(TODAY())))
Hi Sara,
Glad this was of some help! Great question. To be honest, this was a couple years ago and I would need to look at this formula and yours again. It might be a couple of days.