DATEIF 30th v 31st of Month calculating months

Copper Contributor

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

 

Any advice appreciated.

 

Alan

 

 

 

6 Replies

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

 

@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

 

Made my week 

Hi ,
Please help.
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

@linda2022 

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

Thank you so much! It worked. I appreciated.

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