New Contributor

# DATEIF 30th v 31st of Month calculating months

Hi

I am trying to calculate number of months between two dates.

All works well using

=DATEDIF(O4,P4,"m")

eg

31/07/2019 to 31/08/2020 Returns 13 months

However

31/07/2019 to 31/06/2020 Returns 10 months

When I want 11 months

Alan

6 Replies

# Re: DATEIF 30th v 31st of Month calculating months

@alangibson The DATEDIF function is not full proof and only exists to be compatible with old Lotus 1-2-3 files (That's what HELP tells me). It always goes wrong when you try to calculate months between two  month-ends AND the day in the start date is greater than the day in the end date. So, 31 Jan - 29 Feb will give -zero- months. Change the start date to 29 Jan and you'll get 1. If you always want to compare month-ends you could use this formula. I believe it always works.

``=(YEAR(P4)-YEAR(O4))*12+MONTH(P4)-MONTH(O4)``

# Re: DATEIF 30th v 31st of Month calculating months

@Riny_van_Eekelen thank you.

Works a treat.

Its been bugging me for a few months now.

Couldnt work out why it was calculating correct some months and not others. Then i realised it was when i was calculating 30 v 31 etc

# Re: DATEIF 30th v 31st of Month calculating months

Hi ,
I did try to use the formula above, however it is still giving the wrong month.
For example : Start date: 1/1/2023 - End date 1/31/2028. Total should be 61 month, but it gives me 60 months only. My case is my start date is always 1st of the month, but the end date will need to be always end of the month (regardless 30 or 31 or for Feb it will be either 28 or 29)
is there away to fix this ? Thank you in advance

# Re: DATEIF 30th v 31st of Month calculating months

With the start date in A1 (the first day of a month) and the end date in B1 (the last day of another month), you can use

=DATEDIF(A1,B1+1,"m")

# Re: DATEIF 30th v 31st of Month calculating months

Thank you so much! It worked. I appreciated.

# Re: DATEIF 30th v 31st of Month calculating months

Hi

For solving the month end date problem I use a simple if statement IF(DAY(E7)<DAY(C7),then make the next statements.

It is tricky that datedif can be tricky for month end.

Hope this helps

Kind regards

Paul