Excel - using the Datedif Function to generate days

0
votes

In Excel, using the Datedif formula to compare two dates to generate an outcome with a format of x Years, y Months and z Days.

The formula generally looks like:- =DATEDIF(B1,B2,"y")&" years, " & DATEDIF(B1,B2,"ym")&" months, " & DATEDIF(B1,B2,"md")&" days"

However, occasionally this can generate a negative number of days!! Start date End date Generated Output 31/10/1985 01/03/2020 34 years, 4 months, -1 days

Microsoft are aware - https://support.microsoft.com/en-us/office/datedif-function-25dba1a4-2812-480b-84dd-8b32a451b35c - they have a work around, which doesn't work too well either, particularly if the Start date is in a leap year.

Is anyone aware of any other variation or different excel function that does give 100% accurate results?

1
excelformula
Since neither years nor months have a fixed number of days, any solution with 100% accurate results is going to depend on your definition/conventions for dealing with this irregularity. Only after you develop your conventions will you be able to devise a solution to test if it is in accord with your conventions. - Ron Rosenfeld

1 Answers

0
votes

DATEDIF works on consecutive months but not when the 2nd date is more recent than the first date. For example, in order to calculate an overpaid contract:

Contract end date = 8/31/16

Billed through date = 9/30/16

DATEDIF returns #NUM!

The test formula that I posted above can handle negative values except when the two dates don't have the same number of days in the month.