Forum Discussion
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)
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 advanceWith 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")
