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.
𝗦𝘆𝗻𝘁𝗮𝘅
DATEDIF ( 𝚜𝚝𝚊𝚛𝚝_𝚍𝚊𝚝𝚎 , 𝚎𝚗𝚍_𝚍𝚊𝚝𝚎 , 𝚞𝚗𝚒𝚝 )
• 𝚜𝚝𝚊𝚛𝚝_𝚍𝚊𝚝𝚎 — the start date.
• 𝚎𝚗𝚍_𝚍𝚊𝚝𝚎 — the end date.
• 𝚞𝚗𝚒𝚝 — 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.
𝗘𝗿𝗿𝗼𝗿 𝗥𝘂𝗹𝗲𝘀
• #NUM is returned if the 𝚜𝚝𝚊𝚛𝚝_𝚍𝚊𝚝𝚎 is greater than the 𝚎𝚗𝚍_𝚍𝚊𝚝𝚎.
• #VALUE is returned if 𝚜𝚝𝚊𝚛𝚝_𝚍𝚊𝚝𝚎 or 𝚎𝚗𝚍_𝚍𝚊𝚝𝚎 contains an invalid date.
• #NUM is returned if an invalid 𝚞𝚗𝚒𝚝 is specified.
Page 1 shows an example for each DATEDIF 𝚞𝚗𝚒𝚝 based on a 𝚜𝚝𝚊𝚛𝚝_𝚍𝚊𝚝𝚎 of 02/04/2020 and an 𝚎𝚗𝚍_𝚍𝚊𝚝𝚎 of 01/04/2022.
𝗥𝗲𝗺𝗮𝗿𝗸𝘀
• There is no 𝚞𝚗𝚒𝚝 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 𝚎𝚗𝚍_𝚍𝚊𝚝𝚎 is one day greater than its last.
“𝗠𝗗” 𝗥𝗲𝗺𝗮𝗿𝗸𝘀
• Counting doesn’t include the 𝚎𝚗𝚍_𝚍𝚊𝚝𝚎, so the maximum number of days returned is 30.
• If the 𝚜𝚝𝚊𝚛𝚝_𝚍𝚊𝚝𝚎 and 𝚎𝚗𝚍_𝚍𝚊𝚝𝚎 begin on the same day, zero is returned.
• A late January 𝚜𝚝𝚊𝚛𝚝_𝚍𝚊𝚝𝚎 and early March 𝚎𝚗𝚍_𝚍𝚊𝚝𝚎 results in a minus or zero figure.
• The month preceding the 𝚎𝚗𝚍_𝚍𝚊𝚝𝚎 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.
