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

  A B C D E
1 Start date End date Difference Formula Description
2 01/01/2010 15/06/2015 1991 =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

 

Related Articles

Leave a Reply

Your email address will not be published.