Apr 03 2020 04:09 AM
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
Apr 03 2020 04:38 AM
@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)
Apr 03 2020 05:01 AM
@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
Feb 04 2022 02:32 PM
Feb 04 2022 02:46 PM
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")
Feb 04 2022 02:53 PM
Jan 13 2023 03:51 AM
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