DatedIF function calculates the difference in two date values in years, months, or days in several formats which are more user friendly and accurate than subtracting two dates in the regular format.

Quick example:

How many years have you worked for the the company?
Hire Date: 6/12/2019
Today’s Date: 7/20/23
Substracting the two cells gives you 1,499 which is the number of days. To get years you then have to divide by 365. While is maybe good is most cases it’s imprecise and can be problematic in other cases.

Type 5/27/2019 in Cell A1 and format it to a date format. Excel may guess that it’s a date and may format it for you. Let’s call this the Hire date.

Type 4/30/23 in cell A1 and format it to a date format. Let call this the current date.

You need to know how long the employee has been with the company.

In Cell C1 enter the formula: =A2 – A1 which should give you 1,444, the number of days employed. In D1 enter the formula: =C1/365 which is the crude method to determine the number of years employed. which results in 3.956 years.  If you were to quickly round this cell it would display as 4 years.  Yes, you can use the RoundDown function to arrive at 3 years. But envision a full list of, say 100, employees, you would have to review each one to determine if the Round or RoundDown function was proper. So now:

Using the Datedif function your answer will be precise:

In cell D2 enter the formula: =Datedif(a1,a2,”y”) that will result in 3 years.

In cell D3 enter the formula: =Datedif(a1,a2,”m”) that will result in 47 months

In cell D4 enter the formula: =Datedif(a1,a2,”y”) that will result in 1,444 days..

One question that many times comes up is how close is the employee to the next anniversary. Wild decimals don’t go well in the HR meeting.

=DATEDIF(A1,A2,”y”)&” years “&DATEDIF(A1,A2,”ym”)&” months “&DATEDIF(A1,A2,”md”)&”days”   to give you:

3 years 11 months 14 days

Above, the:

Datedif(a1,a2,”y”) results in the number of years
Datedif(a1,a2,”m”) results in the number of months since the hire date
Datedif(a1,a2,”d”) results in the number of days. since the hire date

In the concatenated DatedIf:

Datedif(a1,a2,”y”) results in the number of years
Datedif(a1,a2,”ym”) results in the number of months since the first day of the year
Datedif(a1,a2,”dm”) results in the number of days since the first day of the month

Concatenating the three together and and adding descriptive text gives you much better results

An Excel file showing the in detail is at the file link below.

File  (coming soon)

Of interest:

How do you pronounce DATEDIF?  It looks like “Dated If” but what it really is
Date Dif ference.  This function works perfectly well in all versions of Excel but is not listed in any documentation nor does it show in the autocomplete.  This is an Excel mystery but it may be perhaps that it’s origination was in Lotus 1-2-3 and the Excel folks included it in Excel but not the documentation.