How to Count the Months Between 2 dates?
What is the proper formula to use to count the months between the two dates?
Best Answer
-
Hi, @ECD Auto Design .
The following formula will get you the exact months.
=(NETDAYS([Contract Start Date (Elliot)]@row, [End Date Based On Waterfall (Elliot)]@row)/365)*12
Use...
- = ROUND(xxx,0) for whole months
- = ROUND(xxx,1) for months to 1 decimal place
...substituting the formula above for "xxx". (Remove the "=" from the original formula.)
Why it works...
- NETDAYS() returns the number of days between the start and end dates.
- Dividing that number (days) by 365 converts your unit of measurement from days to years.
- Multiplying the number of years by 12 converts the unit of measurement to months.
Answers
-
@ECD Auto Design try:
=MONTH(Date@row) - MONTH([Date 2]@row) where Date and Date 2 are the two columns with dates.
But you probably need to account for changing years? Perhaps:
=(YEAR(Date@row) - YEAR([Date 2]@row)) * 12 + (MONTH(Date@row) - MONTH([Date 2]@row))
Check a few use cases to make sure that hangs together.
dm
-
=(YEAR(Contract Start Date (Elliot)) - YEAR((End Date Based On Waterfall (Elliot))) * 12 + (MONTH(Contract Start Date (Elliot)) - MONTH(End Date Based On Waterfall (Elliot))
That is what the formula looks like when i put my data into it. Now the only issue with it is it made it a negative number. I think its pretty close but not quite there yet
-
Hi, @ECD Auto Design .
The following formula will get you the exact months.
=(NETDAYS([Contract Start Date (Elliot)]@row, [End Date Based On Waterfall (Elliot)]@row)/365)*12
Use...
- = ROUND(xxx,0) for whole months
- = ROUND(xxx,1) for months to 1 decimal place
...substituting the formula above for "xxx". (Remove the "=" from the original formula.)
Why it works...
- NETDAYS() returns the number of days between the start and end dates.
- Dividing that number (days) by 365 converts your unit of measurement from days to years.
- Multiplying the number of years by 12 converts the unit of measurement to months.
-
@Toufong Vang That was the one! Thank you
-
I know this post is old but I just needed such a formula.
@Toufong Vang Thank you for that formula However, I found that it was providing inconsistent data. I opted for using this formula instead.
(MONTH([End Date]@row) - MONTH([Start Date]@row) + 1)
My use-case:
A task is recurring every 01 of every month. In the examples below you can see that I can't get 3 months unless I pass a certain number of days in the ending month.
Round wouldn't give me the 3 months I needed until the decimal was over .5.
RoundUp would not give me the 3 months I needed until it was over 2.0
RoundDown obviously won't work.
I understand that you can get exact decimals, but in an example where I need to get actual months, only my formula worked. Just wanted to provide another formula in case anyone needed it.
-
Hi, @Emilio Wright , in that case, you'll want to use...
(YEAR([End Date]@row) - YEAR([Start Date]@row))*12 + MONTH([End date]@row) - MONTH([Start Date]@row) + 1
...to account for Start-to-End dates that span across different years (e.g., "12/01/2022 - 01/01/2023").
As it is, your formula will return "-10" when the start date is "12/01/2022" and the end date is "01/01/2023".
-
@Toufong Vang Thank you for the insight on the Year. As our form currently stands, we only allow users to specify one year. For this reason, we won't have values that cross years since the tasks they are entering will be evaluated on a yearly basis.
-
@Toufong Vang I am trying to implement your formula in one of my Smartsheets but it seems to be throwing an extra month in for dates that are in the same month but one year apart. I would be looking for this example below to return 12 months as the result. Any insight on this? Thank you!
-
Hi, @Jags0829, to get the approximate number of months between two dates, use the approach below.
- Use NETDAYS() to find the number of days between the two dates.
- Divide that by 30.417 days/month (365 days divided by 12 months).
- ROUND() it to zero decimal places to return the number of months.
ROUND( (NETDAYS( xxx , xxx)/30.417) , 0 )
ROUND((NETDAYS([Start of Contract]@row, [End of Contract]@row)/30.417), 0)
-
Thank you so much, Toufong Vang, for a very helpful formula to count the months across years!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!