SOLVED

datedif giving random returns

%3CLINGO-SUB%20id%3D%22lingo-sub-1609031%22%20slang%3D%22en-US%22%3Edatedif%20giving%20random%20returns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1609031%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20using%20the%20datedif(BegDate%2CEndDate%2C%22M%22)%20function%20to%20build%20a%20depreciation%20schedule.%20The%20column%20headings%20contain%20the%20current%20date%20for%20the%20depreciation%20(1%2F31%2F20%2C%202%2F29%2F20%2C%20etc.).%20Datedif%20compares%20the%20column%20heading%20to%20the%20acquired%20date%20of%20the%20asset%2C%20in%20this%20case%2012%2F31%2F15.%20My%20problem%20is%20that%20the%201%2F31%2F20%20column%20is%20returning%20the%20same%20number%20as%20the%202%2F29%2F20%20column.%20For%20example%2C%201%2F31%2F20%20returns%2049%2C%20then%202%2F29%2F20%20returns%2049%2C%20then%203%2F31%2F20%20returns%2051.%20As%20I%20dragged%20the%20formula%20over%20it%20frequently%20returns%20the%20same%20number%20twice%2C%20then%20gives%20the%20correct%20number%20in%20a%20third%20row.%20The%20strange%20thing%20is%20that%20this%20function%20worked%20perfectly%20in%20a%20spreadsheet%20I%20put%20together%20a%20few%20days%20ago%20but%20is%20giving%20inconsistent%20results%20today.%20Ideas%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1609160%22%20slang%3D%22en-US%22%3ERe%3A%20datedif%20giving%20random%20returns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1609160%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F769506%22%20target%3D%22_blank%22%3E%40RonG_Fresno%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20you%20have%20found%2C%20DATEDIF%20is%20not%20entirely%20dependable.%20In%20your%20example%2C%20Excel%20returns%2049%20for%2002%2F29%2F2020%20since%2029%20is%20less%20than%2031.%3C%2FP%3E%0A%3CP%3EYou%20might%20use%20a%20custom%20VBA%20function%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3EFunction%20DDif(StartDate%20As%20Date%2C%20EndDate%20As%20Date%2C%20Interval%20As%20String)%20As%20Long%0A%20%20%20%20DDif%20%3D%20DateDiff(Interval%2C%20StartDate%2C%20EndDate)%0AEnd%20Function%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EUse%20like%20this%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DDDif(B1%2C%20C1%2C%20%22m%22)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Ewith%20the%20start%20date%20in%20B1%20and%20the%20end%20date%20in%20C1.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1609295%22%20slang%3D%22en-US%22%3ERe%3A%20datedif%20giving%20random%20returns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1609295%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%20Thanks%20for%20your%20response.%20You%20also%20clued%20me%20in%20that%20the%20problem%20is%20because%20of%20different%20days%20in%20each%20month.%20I%20tried%20using%20datedif()%20with%20a%20%2B3%20(to%20keep%20all%20months%20with%20at%20least%2031%20days)%20after%20the%20end%20date%20and%20it%20seems%20to%20work%20right.%20I%20suspect%20that%20I%20may%20run%20into%20a%20problem%20when%20my%20%2B3%20overlaps%20another%20date%20some%20time.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1609820%22%20slang%3D%22en-US%22%3ERe%3A%20datedif%20giving%20random%20returns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1609820%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F769506%22%20target%3D%22_blank%22%3E%40RonG_Fresno%3C%2FA%3E%2C%20instead%20of%20the%20erratic%20DATEDIF%2C%20wrap%20YEARFRAC%20with%20ROUNDUP%2C%20like%20this%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22DifferenceInMonths.PNG%22%20style%3D%22width%3A%20513px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F214577i7CF5006D7C976A47%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22DifferenceInMonths.PNG%22%20alt%3D%22DifferenceInMonths.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1609857%22%20slang%3D%22en-US%22%3ERe%3A%20datedif%20giving%20random%20returns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1609857%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F769506%22%20target%3D%22_blank%22%3E%40RonG_Fresno%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOne%20other%20possibility%20may%20be%20to%20add%20one%20if%20the%20days%20in%20your%20column%20headings%20is%20less%20than%20the%20days%20in%20the%20acquisition%20date.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1611463%22%20slang%3D%22en-US%22%3ERe%3A%20datedif%20giving%20random%20returns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1611463%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3BThanks.%20That%20worked%20great!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1612473%22%20slang%3D%22en-US%22%3ERe%3A%20datedif%20giving%20random%20returns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1612473%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F769506%22%20target%3D%22_blank%22%3E%40RonG_Fresno%3C%2FA%3E%2C%20you're%20welcome!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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?

6 Replies
Highlighted

@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.

Highlighted

@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. 

Highlighted

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

DifferenceInMonths.PNG

Highlighted

@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.

Highlighted

@Twifoo Thanks. That worked great!!

Highlighted
Best Response confirmed by RonG_Fresno (New Contributor)
Solution

@RonG_Fresno, you're welcome!