How to Count the Months Between 2 dates?

ECD Auto Design

What is the proper formula to use to count the months between the two dates?

Toufong Vang

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.

Dale Murphy

@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

ECD Auto Design

=(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

Toufong Vang

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.

ECD Auto Design

@Toufong Vang That was the one! Thank you

Emilio Wright

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.

Toufong Vang

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

Emilio Wright

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

Jags0829

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

Toufong Vang

Hi, @Jags0829, to get the approximate number of months between two dates, use the approach below.

1. Use NETDAYS() to find the number of days between the two dates.
2. Divide that by 30.417 days/month (365 days divided by 12 months).
3. 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)

Amy Evans

Thank you so much, Toufong Vang, for a very helpful formula to count the months across years!