Forum Discussion

alangibson's avatar
alangibson
Copper Contributor
Apr 03, 2020

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

 

Any advice appreciated.

 

Alan

 

 

 

excel
Formulas and Functions
  • Paul_Aberdeen's avatar
    Paul_Aberdeen
    Copper Contributor
    Jan 13, 2023

    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

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Apr 03, 2020

    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)

     

    • alangibson's avatar
      alangibson
      Copper Contributor
      Apr 03, 2020

      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 

      • linda2022's avatar
        linda2022
        Copper Contributor
        Feb 04, 2022
        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

Resources