Issue with the DATEDIF function
Solved
Lili03100
Posted messages
157
Status
Membre
-
Raymond PENTIER Posted messages 58546 Registration date Status Contributeur Last intervention -
Raymond PENTIER Posted messages 58546 Registration date Status Contributeur 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
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 réponses
Good evening
=DATEDIF(A1,A2+1,"m")
Best regards
Via
--
"Imagination is more important than knowledge." A. Einstein
=DATEDIF(A1,A2+1,"m")
Best regards
Via
--
"Imagination is more important than knowledge." A. Einstein
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.
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.
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.
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.