Calculate the Difference Between Dates
The easiest way to find the difference between two dates is to subtract the older date from the newer one. That will return the number of days; however, you may want it in weeks, months or years.
DATEDIF is a flexible function that caters for these, although you’d be forgiven for not knowing it exists, as it doesn’t appear in the Formula AutoComplete list. Microsoft has deliberately kept it undocumented due to the erroneous results it produces in certain circumstances. Used correctly, though, and it’s still a handy function that has not been superseded.
Syntax
DATEDIF ( 𝚜𝚝𝚊𝚛𝚝_𝚍𝚊𝚝𝚎 , 𝚎𝚗𝚍_𝚍𝚊𝚝𝚎 , 𝚞𝚗𝚒𝚝 )
start_date
— the start date.end_date
— the end date.unit
— which time unit the date difference is returned in. The choices are:- D — difference in days.
- M — difference in complete months.
- Y — difference in complete years.
- MD — difference in days, ignoring months and years.
- YD — difference in days, ignoring years.
- YM — difference in months, ignoring days and years.
Error Rules
- #NUM is returned if the
start_date
is greater than theend_date
. - #VALUE is returned if
start_date
orend_date
contains an invalid date. - #NUM is returned if an invalid
unit
is specified.
Page 1 shows an example for each DATEDIF unit
based on a start_date
of 02/04/2020 and an end_date
of 01/04/2022.
𝗥𝗲𝗺𝗮𝗿𝗸𝘀
• There is no unit
for weeks, so the workaround is to find the difference in days and divide this figure by seven.
• DATEDIF always rounds down, which is why the difference is one year, despite only being a day short from two.
Page 2 contains additional examples for the “M” and “MD” units that highlight the problems you might face.
“𝗠” 𝗥𝗲𝗺𝗮𝗿𝗸𝘀
• Two whole months fit in between 29/12/2021 and 28/02/2022, but only one is recognised.
• A month starting from the first day is only counted if the end_date
is one day greater than its last.
“𝗠𝗗” 𝗥𝗲𝗺𝗮𝗿𝗸𝘀
• Counting doesn’t include the end_date
, so the maximum number of days returned is 30.
• If the start_date
and end_date
begin on the same day, zero is returned.
• A late January start_date
and early March end_date
results in a minus or zero figure.
• The month preceding the end_date
is prioritised, so February is treated as a 31-day month in some calculations.
The takeaway from this is to always be aware of the numbers that DATEDIF returns. It is advisable to seek alternative methods if you wish to use the “M” or “MD” units.