Difference of Two Dates with Exact Year(s), Month(s) and Day(s) in Excel

by | Jul 5, 2020 | Date & Time, Formula & Functions

Goal:

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.

Algorithm:

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 1st January, 1900; 2 for 2nd 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

Formula:

Suppose, the dates you want to calculate are 1st January, 2009 and 2nd 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)”)

Caution:

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”

Tip:

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

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

By Rajesh K Saraogi

Rajesh is a hardcore finance person with a penchant for latest technology. His passions are latest technology and to improve the productivity with simple, yet powerful software – mainly on Microsoft Excel platform. He is a qualified MCA. Whenever he gets time he loves to blog, to share the knowledge, which is the only thing which increases after sharing!

Share This

Share this post with your friends!