DATEDIF: Difference Between Two Dates
Most people arrive here for a gap in days, and that one needs no function at all. Plain subtraction answers it, and the only thing that goes wrong is the cell inheriting a date format from its column and reporting the answer as a date. DATEDIF earns its keep further along, where the unit has to be whole months or whole years, and it brings two traps of its own — the earlier date goes first or it errors, and Microsoft documents the leftover-days unit as unreliable. It is invisible as well: no autocomplete, no tooltip when the bracket opens. That is not a fault, just a function nobody advertises.
The formula
=B2-A2 days between two dates (format the result General, not Date)
=DATEDIF(A2,B2,"D") the same day count, spelled out
=DATEDIF(A2,B2,"M") complete months elapsed
=DATEDIF(A2,B2,"Y") complete years elapsed
=DATEDIF(A2,B2,"YM") months left over after the whole years
=B2-EDATE(A2,DATEDIF(A2,B2,"M")) days left over after the whole months — safer than "MD"
=NETWORKDAYS(A2,B2) working days only, weekends droppedA worked example
A2 holds 15 March 2024, B2 holds 1 September 2026, and the gap has to be reported the way a person would say it rather than as a lump of days.
=DATEDIF(A2,B2,"Y") & " years, " & DATEDIF(A2,B2,"YM") & " months"
"2 years, 5 months". "Y" gives the completed years and "YM" gives the months left over on top of them, which is why the two units are almost always used as a pair. The same two dates are 900 days apart — =B2-A2 will tell you so, but nobody reads 900 days as two and a half years, and that is the whole reason a function nobody can autocomplete is still worth typing.
Which one do I need?
| If you want to… | Use |
|---|---|
| How many days lie between two dates | =B2-A2. If the answer appears as a date, the cell inherited a date format from the column — set it to General and the number appears. |
| Complete months or complete years, not days | =DATEDIF(A2,B2,"M") or =DATEDIF(A2,B2,"Y") — both count only whole units and discard the remainder |
| Years and the leftover months together, the way an age or a tenure is spoken | Pair "Y" with "YM" and join them with & |
| The leftover days after the whole months | Avoid the "MD" unit: =B2-EDATE(A2,DATEDIF(A2,B2,"M")) gives the same answer without its month-end defect |
| Someone's age from their date of birth | =DATEDIF(A2,TODAY(),"Y") — with the caveat that TODAY() is volatile, so the figure moves on its own |
| Only working days should count | =NETWORKDAYS(A2,B2), or NETWORKDAYS.INTL when the weekend is not Saturday and Sunday, or when a holiday list has to come out too |
| The two values are timestamps and the answer wanted is hours and minutes | DATEDIF truncates to whole days and is no use here |
| One or both "dates" refuse to subtract at all | They are text, not dates — Excel cannot do arithmetic on them until they are converted |
Frequently asked questions
Why does Excel not suggest DATEDIF when I start typing it?
DATEDIF is deliberately absent from the function list, from IntelliSense and from the Insert Function dialog. It was kept for compatibility with Lotus 1-2-3 and Microsoft has never promoted it to a documented, discoverable function, so the formula engine calculates it perfectly while the interface pretends it does not exist. Type the name and the arguments in full and press Enter — the absence of a tooltip is not an error.
Why does my DATEDIF formula return #NUM!?
Almost always because the dates are in the wrong order. The signature is DATEDIF(start_date, end_date, unit), and an end date earlier than the start date produces #NUM! rather than a negative number. Swap the two cell references. A unit string DATEDIF does not recognise — anything outside "Y", "M", "D", "YM", "MD" and "YD" — returns #NUM! as well.
Is the "MD" unit safe to rely on?
No, and Microsoft says so in its own documentation for the function: "MD" may return a negative number, a zero, or an inaccurate result. Month-end start dates such as 31 January are where it goes wrong, because subtracting the day numbers alone crosses a month that has no matching day. Use =B2-EDATE(A2,DATEDIF(A2,B2,"M")) instead — it advances the start date by the whole months already counted and subtracts, so the leftover can never come out negative.
Should the day count include both the start and the end date?
Neither subtraction nor DATEDIF thinks so: 1 March to 2 March is 1, because they measure the gap, not the number of dates in the range. If you are counting days worked, days billed or nights stayed and both ends should count, add 1 explicitly — =B2-A2+1. Getting this wrong by one day is the most common error in date arithmetic and neither formula will warn you about it.
New guides and tools, once a month
DE + EN · double opt-in · no spam