View original (French)

Issue with the DATEDIF function

Solved
Lili03100 Posted messages 157 Status Member -  
Raymond PENTIER Posted messages 58213 Registration date   Status Contributor Last intervention   -
Good evening,

I am having an issue with the DATEDIF function: when I want to calculate the difference in months between January 1, 2021, and December 31, 2021, using the following function in cell B1 =DATEDIF(A1;A2;"m"), the result is 11 months instead of 12 months.

Do you know how I can solve this?

Configuration: Windows / Chrome 92.0.4515.131

3 answers

  1.
    Raymond PENTIER Posted messages 58213 Registration date   Status Contributor Last intervention   17 482
     
    Hello Lili.

    In its logic, the DATEDIF function correctly counts 11 months by going from December to January, meaning 12-1.
    Instead, use the formula =DATEDIF(A1;A2;"j")/12

    --
    Retirement is great! Especially in the Antilles...
    Raymond (INSA, AFPA)
    1
    1. brucine Posted messages 24861 Registration date   Status Member Last intervention   4 172
       
      Hello,

      No, because DATEDIF is in the Anglo-Saxon format: D and not J, and it is necessary (whole number format) to divide not by 12 but by 30.
      0
    2. Raymond PENTIER Posted messages 58213 Registration date   Status Contributor Last intervention   17 482
       
      Please excuse me: brucine corrected my oversight!
      0
  2. via55 Posted messages 14393 Registration date   Status Member Last intervention   2 759
     
    Good evening

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

    Best regards
    Via

    --
    "Imagination is more important than knowledge." A. Einstein
    0
  3. Lili03100 Posted messages 157 Status Member 1
     
    Thank you so much Via55, but it's not working if, for example, I put 01/28/2021 and 12/31/2021; it shows 11 months.
    0
    1. brucine Posted messages 24861 Registration date   Status Member Last intervention   4 172
       
      Good evening,

      Via55 is right: DATEDIF does not include the “unfinished” date (the last date) in the calculation; to account for this starting from January 1st, you either have to add 1 to the end date or set it not to December 31st but to January 1st of the following year.

      Now, if I start from January 28th, I contradict this behavior because whether I choose +1 or manually set it to 01/01/22 or 31/12/21, the result is the same, 11 months and correct, showing that the “unfinished date” is now taken into account.

      Microsoft itself warns of the erratic behavior of DATEDIF, which only exists for Lotus backward compatibility.

      It seems healthier to use:

      =MAX(0;(YEAR(B2+1)-YEAR(A2-1))*12+MONTH(B2+1)-MONTH(A2-1)-1)

      which yields a consistent result in both cases.
      0
      1. Lili03100 Posted messages 157 Status Member 1 > brucine Posted messages 24861 Registration date   Status Member Last intervention  
         
        Thank you, Brucine, for this clarification.
        0