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 |