MS Excel Tips: Find The Difference Between Two Dates Using DATEDIF

This is a little known function in Excel in recent years even though it has been available in all versions since 95, it is then documented only in the Help file of 2000 but no further documentation of it in any other versions.
Nevertheless this function is mighty useful for calculating an employees age, length of service or the length or duration of a loan.
This Excel function actually stands for DATED DIFFERENCE, ie it finds the difference between two dates. The dates can be calculated in a number of variables, that is year, month, day or hours.
The way to use this function or it's syntax is as follows
=DATEDIF (Date1, Date 2,Interval)
Where

• Date1 is the first date
• Date2 is the second date
• Interval is the type of interval to return i.e days, months, years

There are a number of codes to insert into the function for the date interval and yours should be one of the below:-

• m = months (complete calendar months between the two dates)
• d = days (number of days between the two dates)
• y = years ( number of complete calendar years between the two dates
• ym = calendar months between the dates as if they were in the same year
• yd = complete calendar days between the dates as if in the same year
• md = complete calendar days between the dates as if they were in the same month and year.

Here is a basic example
To calculate the length of service for an exmployee
In cell A1 type 01/12/1990
In cell B1 type 15/11/2010
In cell A5 type the formula =DATEDIF(A1,B1,"Y")
Your result will be 19.
A few points to note

• If the interval you stated is not one of the above options then Excel will return the #NUM error.
• If Date 1 is later than Date 2 Excel will return the #NUM error.
• If Date 1 or Date 2 is not a valid date then the Excel will return the #VALUE error.
• If including the Interval string i.e Y, M D etc directly in the formula then you need to enclose it in double quotes "".

• If you have the interval in another cell i.e =DATEDIF(A1,B1,C2) then there is no need for double quotes. In this case its in C2

Let's turn up the volume on this. For the length of service of an employee you would normally want to know the days, months and years of service, the formula would look like this:-
=DATEDIF(A1,B1,"y")&" years "&DATEDIF(A1,B1,"ym")&" months"&DATEDIF(A1,B1,"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 like 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,B1,"y")=0,"",DATEDIF(A1,B1,"y")&" years ")&IF(DATEDIF(A1,B1,"ym")=0,"",
DATEDIF(A1,B1,"ym")&" months ")&DATEDIF(A1,B1,"md")&" days"
A great formula- give this one a go.

Article Source: http://EzineArticles.com/6867237