datedif giving random returns

[RonG_Fresno]

I am using the datedif(BegDate,EndDate,"M") function to build a depreciation schedule. The column headings contain the current date for the depreciation (1/31/20, 2/29/20, etc.). Datedif compares the column heading to the acquired date of the asset, in this case 12/31/15. My problem is that the 1/31/20 column is returning the same number as the 2/29/20 column. For example, 1/31/20 returns 49, then 2/29/20 returns 49, then 3/31/20 returns 51. As I dragged the formula over it frequently returns the same number twice, then gives the correct number in a third row. The strange thing is that this function worked perfectly in a spreadsheet I put together a few days ago but is giving inconsistent results today. Ideas?

[#2]

@RonG_Fresno 

As you have found, DATEDIF is not entirely dependable. In your example, Excel returns 49 for 02/29/2020 since 29 is less than 31.

You might use a custom VBA function:

Function DDif(StartDate As Date, EndDate As Date, Interval As String) As Long
    DDif = DateDiff(Interval, StartDate, EndDate)
End Function

Use like this:

=DDif(B1, C1, "m")

with the start date in B1 and the end date in C1.

[#3]

@Hans Vogelaar  Thanks for your response. You also clued me in that the problem is because of different days in each month. I tried using datedif() with a +3 (to keep all months with at least 31 days) after the end date and it seems to work right. I suspect that I may run into a problem when my +3 overlaps another date some time. 

[#4]

@RonG_Fresno, instead of the erratic DATEDIF, wrap YEARFRAC with ROUNDUP, like this:

[#5]

@RonG_Fresno 

One other possibility may be to add one if the days in your column headings is less than the days in the acquisition date.

[#6]

@Twifoo Thanks. That worked great!!

[#7 · Accepted solution]

@RonG_Fresno, you're welcome!