Excel – How to calculate the difference between two dates

The following formulas show how to calculate the difference between two dates.

The difference can be measured by:

  • days
  • months
  • years
  • days – ignoring years
  • days – ignoring months and years
  • months – ignoring years
  • combined years, months, days

Note: the DATEDIF function DOES NOT round calculations – so only whole figures are given.

* examples below use date format DD/MM/YYYY

 ABCDE
1Start dateEnd dateDifferenceFormulaDescription
201/01/201015/06/20151991=DATEDIF( A2, B2, “D” )days
3  65=DATEDIF( A2, B2, “M” )months
4  5=DATEDIF( A2, B2, “Y” )years
5  165=DATEDIF( A2, B2, “YD” )days – ignoring years
6  14=DATEDIF( A2, B2, “MD” )days – ignoring months and years
7  5=DATEDIF( A2, B2, “YM” )months – ignoring years
8  5 Years, 5 Months, 15 Days=IF(DATEDIF(A2,B2,”Y”)=1,DATEDIF(A2,B2,”Y”)&” Year, “,DATEDIF(A2,B2,”Y”)&” Years, “)&IF( DATEDIF(A2,B2,”YM”)=1,DATEDIF(A2,B2,”YM”) & ” Month, “,DATEDIF(A2,B2,”YM”)& ” Months, “) &IF(DATEDIF(A2,B2,”MD”)=1,DATEDIF(A2,B2,”MD”)&” Day”,DATEDIF(A2,B2,”MD”)&” Days”)combined years, months, days

 

Was this article helpful?

Related Articles