Date Difference Calculator with Excel Formulas
Days, months, years and working days between two dates, with the DATEDIF and NETWORKDAYS formulas.
3 years, 2 months and 5 days
=B2-A2=DATEDIF(A2,B2,"M")=DATEDIF(A2,B2,"Y")=NETWORKDAYS(A2,B2)=DATEDIF(A2,B2,"Y")&" years, "&DATEDIF(A2,B2,"YM")&" months, "&B2-EDATE(A2,DATEDIF(A2,B2,"M"))&" days"The formulas, explained
Excel stores dates as numbers, so subtracting one date from another gives the number of days: =B2-A2. Format the result cell as General or Number, or it may display as a date.
DATEDIF(start, end, unit) counts whole units between two dates. It is an older function that Excel still supports, though it does not appear in the function list and autocomplete will not suggest it. The units are:
| Unit | Returns |
|---|---|
"Y" |
complete years |
"M" |
complete months |
"D" |
days |
"YM" |
months left over after the complete years |
"YD" |
days left over after the complete years |
"MD" |
days left over after complete months (Microsoft warns this unit can give wrong results) |
Because of that warning, the “years, months, days” formula above works out the leftover days with B2-EDATE(A2,DATEDIF(A2,B2,"M")) instead of the "MD" unit.
Working days
NETWORKDAYS(start, end) counts Monday to Friday, including both the start and end date. Add a range of holiday dates as a third argument to skip them: =NETWORKDAYS(A2,B2,H2:H12). For a different weekend, use NETWORKDAYS.INTL.
Common problems
- #NUM! from DATEDIF: the start date is later than the end date.
- #VALUE!: one of the cells holds text that looks like a date. Convert it with
DATEVALUEor Data > Text to Columns. - A month count that looks one short: DATEDIF counts only whole months, so 31 January to 28 February is 0 months and 28 days.
Sources: Function behaviour: Microsoft Support function reference. Results checked against worked examples, and generated formulas evaluated in the open-source HyperFormula engine. Method & sources