Aug 24 2020 12:57 PM
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?
Aug 24 2020 02:11 PM
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.
Aug 24 2020 02:57 PM
@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.
Aug 24 2020 11:39 PM
@RonG_Fresno, instead of the erratic DATEDIF, wrap YEARFRAC with ROUNDUP, like this:
Aug 24 2020 11:56 PM
One other possibility may be to add one if the days in your column headings is less than the days in the acquisition date.