Friday, 18 November 2011

Excel How to find difference between two dates (in years/months/days)

How many ( complete ) years are there between 15-Oct-1983 to 19-Jan-2010?

In Excel:
In cell A2 input 15-Oct-1983 or 15/Oct/1983
In cell B2 input 19-Jan-2010 or 19/Jan/2010
In cell C2 type the formula =DATEDIF(A2,B2,"y")


Notes: 
The "y" in the formula can be in upper or lower case. It SHOULD be in DOUBLE quotes. 
The function DATEDIF can be entered in lower case of upper case but should be spelt correctly. 

Hint for months and days:
To get difference in completed months, replace "y" with "m" in the formula
To get difference in completed days, replace "y" with "d" in the formula

Note 1: If we use DATEDIF to calculate the number of days between 15-Mar-11 and 18-Mar-11 using "d" as the third argument in the formula, the answer will be 3 (i.e. it will exclude the end day). If we intend to include the end day in the calculation, then we should use the formula =DATEDIF(A2,B2,"d") + 1
 

Note 2: From Excel 2007 onward, the DATEDIF function is built into Excel. For Excel 2003 and earlier, go to Tools-->Add Ins--> Analysis Tool Pack --> OK to enable the function.