Forum Discussion
datedif giving random returns
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?
RonG_Fresno, you're welcome!
6 Replies
One other possibility may be to add one if the days in your column headings is less than the days in the acquisition date.
RonG_Fresno, instead of the erratic DATEDIF, wrap YEARFRAC with ROUNDUP, like this:
Twifoo Thanks. That worked great!!
RonG_Fresno, you're welcome!
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 FunctionUse like this:
=DDif(B1, C1, "m")
with the start date in B1 and the end date in C1.
HansVogelaar 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.
