Why is the Excel DateDif function returning 0 months for dates between "4/30/2020" and "3/31/2020"?

0
votes

Microsoft says the DateDif function: =DATEDIF(A3,B3,"M") returns the full number of months between those two dates assuming A3 = Starting month and B3 = Ending month.

However, for dates between 4/30/2020 and 3/31/2020, I am getting a value of 0. I was expecting to get a value of 1. DateDif between 3/1/2020 and 5/31/2020 returns 2, while datedif between 4/30/2020 and 5/31/2020 returns 1 - both of which makes sense to me. So why is the DATEDIF between 4/30/2020 and 3/31/2020 returning 0?

2
excelexcel-formuladate-difference
From the SUPPORT PAGE: Warning: Excel provides the DATEDIF function in order to support older workbooks from Lotus 1-2-3. The DATEDIF function may calculate incorrect results under certain scenarios. There is a reason that it is hidden. It does not work properly in all cases and they are not going to fix it. – Scott Craner
DATEDIF is using the first date to determine the end of month. Since there are more days in your start month than in the end month, it decides a full month has not elapsed. One of its known limitations (albeit not mentioned on the support page). – Rory

2 Answers

3
votes

one can create their own version and use vba's DateDiff:

Function MyDateDif(srt As Date, ed As Date, str As String) As Long
    MyDateDif = DateDiff(str, srt, ed)
End Function

Then you would use it:

=MyDateDif(A3,B3,"M")

Which returns 1 as it should

enter image description here

2
votes

You could use ROUNDDOWN and YEARFRAC to calculate the result. This will return a decimal and round down to the nearest whole number. I guess the moral of the story is to not use deprecated functions.

=ROUNDDOWN(YEARFRAC(A3,B3)*12,0)