This is an old function in Excel that you can’t really find any help on recently , but its a great one, to find the difference between two dates in a number of different variables such as years, months, days, hours etc.

The function actually stands for Date Difference and is really useful for calculating employees age, length of service of an employee or the length of a loan etc.

The way to use this function is

=DATEDIF(Date1, Date2, Interval), where Date1 is the first date, Date2 is the second date and the Interval is the interval type to return.

There are a few different codes you can use for the Interval and a summary can be seen below,

As usual it’s way easier with an examples.

1. Let’s calculate the length of service for an employee in years.

In cell A1 type 01/12/1990

In cell A2 type 15/11/2010

In cell A5 type your formula DATEDIF(A1,A2,”Y”), this should give you an answer of 19.

Go ahead and try the rest of the intervals, the answers you should get can be seen below

M=239

D=7289

Y=19

YM=11

YD=349

MD=14

Now, this all may look at little too literal, and it usually is the case that for payroll or length of service you would want to know then years, months and days a person has worked, that of course can be done in Excel just by turning up the volume on the formula.

So, if we use the same example that we want to find out the years, months and days that someone had worked for a company for payroll reasons then your formula would look like this.

**DATEDIF****(A1****,****A2****,”y”****)****&” years “&DATEDIF****(A1****,****A2****,”ym”****)****&” months “&DATEDIF****(A1****,****A2****,”md”****)****&” Days”**

The result in this instance would be 19 Years, 11 Months and 14 Days. However, if your result of the formula contains a zero for exmaple

0 Years, 14 Months and 2 Days then you might want to supress your zeros so you get the result 14 Months and 2 Days only. Again with a little tweaking of the formula we can achieve this by adding in a sneaky IF statement and your formula would look like this:-

**=IF(DATEDIF(A1,A2,”y”)=0,””,DATEDIF(A1,A2,”y”)&” years “)&IF(DATEDIF(A1,A2,”ym”)=0,””,
DATEDIF(A1,A2,”ym”)&” months “)&DATEDIF(A1,A2,”md”)&” days”**

If the interval is typed into the formula then as it is a string, it needs to be set in “” double quotes, if it is referred to in a cell reference there is no need and can left without the “” double quotes.

tamie says

this did not work for me, can you help?

I am working on cemetery records where i have the date of birth and the date of death and i want it to calculate how many years, months, and days this person lived

birth death age

c6 d6 = e6

9/4/1855 12/30/1939

I tried

=datedif(c6,d6,”Y”),(c6,d6,”YM”),(c6,d6,”MD) and so many others. i just don’t get it.

Barbara says

Tamie

You have a good point, dates before 1900 do not exist in Excel world! hence you are getting the error. You can follow the link to Microsoft’s site where they have a specific macro to help.

http://support.microsoft.com/?kbid=245104

OR

John Walkenbach has an Excel Add In

http://j-walk.com/ss/excel/files/xdate.htm

Regards

Barbara

Yasmeen says

In Excel 2010 the DATEDIF Formula why not working with the dates which entered in different column?

Barbara says

Hi Yasmeen

The DATEDIF should work equally well in Excel 2010, it does not however appearin the functions list you just have to manually type the formula.

Regards

Barbara

Melinda says

DATEDIF is not working in Excel2010 for me either. I know it works in other versions as I have used it before. Very strange.

Barbara says

Hello Melinda all should be good with the DATEDIF even in Excel 2010, can you post your formula?

Jewel says

Date of join 23/12/2015 and date of leave 6/1/2016

after implement above formula its showing total service length is 1 month 128 days

please advice

Barbara says

Hello

I have replicated your formula with the above dates. With the formula

=DATEDIF(A1,A2,”D”) gives result of 14.

Where A1 and A2 contain your fist and second date respectively.

If you still get the spurious result send me copy of your work book.

Thanks

Barbara

ctsondemand.com says

Use the DATEDIF function when you want to calculate the difference between two dates.

Manoj Kumar says

DATEDIF is not actual difference

01-05-1998

31-05-2032

=IF(DATEDIF(A1,A2,”y”)=0,””,DATEDIF(A1,A2,”y”)&” years “)&IF(DATEDIF(A1,A2,”ym”)=0,””,

DATEDIF(A1,A2,”ym”)&” months “)&DATEDIF(A1,A2,”md”)&” days”