Days Between Two Dates in Excel (and DATEDIF)

The simplest days between dates Excel formula is plain subtraction: the later date minus the earlier date. For the same answer with a function, use DAYS. For whole months or years, such as tenure, use DATEDIF. This guide shows all three on one HR sheet, with the units, the argument order and the errors to watch for.

How do you subtract dates in Excel?#

Excel stores dates as serial numbers that count days, so subtracting two dates gives the number of days between them. Put the later date first:

=C2-B2

Here B is the hire date and C is the last review:

If the result looks like a strange date, such as 12/4/1907 (December 4, 1907), Excel has given the cell a date format. Change it to General (Ctrl+Shift+~ on Windows, Control+Shift+~ on a Mac) to see the number of days.

How the dates in B and C are displayed depends on your regional settings, but the subtraction works the same everywhere. To type a date inside a formula, use DATE, like =C2-DATE(2016,4,11), so it can't be misread.

Does Excel count the first day?#

No. Subtraction counts the days from the start to the end, so Monday to Friday of the same week is 4. If both the first and the last day should count, as in "a booking from the 1st to the 5th is 5 days", add 1:

=C2-B2+1

For weeks, divide by 7: =(C2-B2)/7 gives 413.57 weeks for Jordan, and =INT((C2-B2)/7) gives 413 whole weeks.

How to use the DAYS function#

DAYS does the same job as subtraction, with the end date first:

=DAYS(end_date, start_date)
=DAYS(C2,B2)

For Jordan, both give 2895. DAYS has one advantage: it can read dates typed as text, like =DAYS("2024-03-15","2016-04-11"), and still give a number. But text dates depend on your regional settings, so cell references or DATE are safer. If you get a negative number, the dates are the wrong way round. DAYS needs Excel 2013 or later.

How to use DATEDIF for months and years#

DATEDIF counts complete years, months or days between two dates. The start date comes first, the opposite of DAYS:

=DATEDIF(start_date, end_date, unit)

The unit is a code in double quotes:

"Complete" matters. Jordan started on April 11, and his review was on March 15, so his eighth year wasn't finished. He gets 7, not 8. That's why =YEAR(C2)-YEAR(B2), which gives 8, is the wrong answer for tenure.

Combine units to write tenure as text:

=DATEDIF(B2,C2,"Y")&" yrs, "&DATEDIF(B2,C2,"YM")&" mos"

This gives "7 yrs, 11 mos" for Jordan. Microsoft notes that the "MD" unit can give wrong results in some cases, so avoid it in anything important. For more ways to use these units, see how to calculate age or years of service, or read the full DATEDIF function guide.

How do you count the days from a date until today?#

Use TODAY() as the later date. =TODAY()-B2 gives the days since B2, and =B2-TODAY() gives the days left until a future date in B2, such as a deadline. A negative result means the date has already passed.

TODAY() updates every time the file recalculates, so these answers change each day. That's right for a live tracker, but not for a report you need to check later. See TODAY vs NOW in Excel for the difference between the two.

Why doesn't Excel suggest DATEDIF?#

DATEDIF is an older function that Excel keeps for compatibility with other spreadsheet programs. It works in every current version on Windows and Mac, but it isn't listed in the function suggestions and Excel doesn't show its arguments as you type. Type the whole name and the brackets yourself. If you see #NAME?, check the spelling: it's DATEDIF, with one F.

Which days between dates Excel formula should you use?#

Pick the method by the answer you need:

Working days need a different function, because subtraction counts weekends too. See NETWORKDAYS and WORKDAY. To move forward by months rather than measure a gap, see EDATE and EOMONTH.

What are the common mistakes?#

Tenure up to today uses TODAY() as the end date: =DATEDIF(B2,TODAY(),"Y"). That's right for a live HR sheet. For a report or a test answer that must stay the same, put the date in a cell with DATE and point at that instead. Tenure questions come up often in HR roles; see the Excel skills HR jobs ask for.

Practise it#

Try the DAYS exercise on this page, then practise days between dates and DATEDIF in a live spreadsheet. You'll subtract dates, use DAYS, count years and months with DATEDIF, and build a "7 yrs, 11 mos" tenure label.