Tuesday, November 15, 2011

Excel Tips : Find the Difference Between Two Dates

 Find the Difference Between Two Dates


Excel help doesn't talk about this function, i is a great way to find the difference between two dates.
The syntax is =DATEDIF(EarlierDate,LaterDate,"Code")
Here are the valid values that you can use for Code.
  • Y - will tell you the number of complete years between the two dates.
  • YM - will tell you the number of complete months, excluding the years, between the two dates.
  • MD - will tell you the number of complete days, excluding complete months, between the two dates.
  • M - will tell you the number of complete months. For example, have been alive for 495 months
  • D - will tell you the number of days. For example, have been alive for 15,115 days. This is a trivial use, since you could just subtract one date from another and format as a number to duplicate this code.
The useful codes are the first three codes. On the show,  demonstrated this worksheet. Identical formulas in columns D, E, and F calculate the DATEDIF in years, months, and days. 
The formula in column G strings this together to create text with the length of time in years, months, and days. 
You could combine this into a single formula. If cell A2 contains the joining date, use the following formula in B2:
=DATEDIF($A2,TODAY(),"Y")&" years, "&DATEDIF($A2,TODAY(),"YM")&" months & "&DATEDIF($A2,TODAY(),"MD")&" days"

Courtesy : http://www.mrexcel.com/tip137.shtml