To get difference between two dates with the answer as exact year, month, and days. Simply it means, if we take two dates as 1-Jan-2009 and 2-Feb-2010, the required result (difference) will be 1 year 1 month 1 day.

First of all we have to check whether there is completed year(s) and/or completed month(s) between the two dates. If so, we have to calculate completed year(s) and month(s) between the dates, and then days, if any

This can be achieved by Excel’s built-in functions

Excel stores the date in sequential number; e.g., 1 for 1^st January, 1900; 2 for 2^nd January, 1900; and so on. It makes it very easy to get the difference between two dates. Just subtract a date from another and you will get the number of completed days elapsed

We can get difference of two dates in year using function “YEAR”; however, to achieve our goal in this case we will use Excel’s built-in function “DATEDIF”

If you are new to excel, you may not have heard about “DATEDIF”. In fact, this is a hidden function and Microsoft has not covered it in list after 2000, don’t know why. However, this function works in later versions also, may be Microsoft has kept it for compatibility purpose

The syntax for the “DATEDIF” is as under:

DATEDIF(start_date, end_date, unit)

All the arguments are mandatory. You can use unit as “Y”, “M”, “D”, “MD”, “YD” and “YM” for calculating “Completed Years”, “Completed Months”, “Days Elapsed”, “Days Excluding Years & Months”, “Days Excluding Years” and “Months Excluding Days & Years” respectively

Suppose, the dates you want to calculate are 1^st January, 2009 and 2^nd February, 2010; in cells A1 and A2, then enter following formula in any cell for required result:

=DATEDIF(A1,A2,”Y”)&” Year(s) “&

DATEDIF(A1,A2,”YM”)&” Month(s)”&

DATEDIF(A1,A2,”MD”)&” Day(s)”

Your answer will be 1 Year 1 Month 1 Day.

Now, in case there are only months and days, or only months, or only days, or only year, or only year and days, and so on, and to suppress that “0” year, and/or “0” month, and/or “0” day, you may tweak the formula like this:

 =IF(DATEDIF(A1,A2,”Y”)=0,””,DATEDIF(A1,A2,”Y”)&” Year(s) “)&

IF(DATEDIF(A1,A2,”YM”)=0,””,DATEDIF(A1,A2,”YM”)&” Month(s)”)&

IF(DATEDIF(A1,A2,”MD”)=0,””,DATEDIF(A1,A2,”MD”)&” Day(s)”)

A bug was noticed in “DATEDIF” while using “MD”, in excel 2007 & above, sometime it shows the answer as “-1”. To avoid this bug, you may replace last portion of the formula containing “MD” with below formula

=A2-EDATE(A1,(DATEDIF(A1,A2,”Y”)*12+DATEDIF(A1,A2,”YM”)))&” Day”

To calculate your exact age, insert your date of birth in cell “A1”, and replace the “A2” with TODAY() in above formula.