DAYS vs DATEDIF vs Subtracting Dates in Excel

DAYS vs DATEDIF vs simple subtraction is a common source of confusion, because all three can count the days between two dates. Subtracting (=C2-B2) and DAYS (=DAYS(C2,B2)) always give the same number of days. DATEDIF is the one to use when you need complete years or months, such as tenure, age or contract length. This guide is for anyone in HR, admin or finance who works with hire dates, deadlines or birthdays and wants the right formula the first time.

DAYS vs DATEDIF vs subtraction at a glance#

Subtraction and DAYS count days only; DATEDIF counts days, months or years.

Why subtracting dates works#

Subtraction works because Excel stores every date as a whole number: the number of days since a fixed starting point. In the default Windows date system, 1 January 1900 is day 1 and 1 January 2025 is day 45658. Subtracting one date from another simply subtracts those numbers, which gives the days between them. Our guide to why Excel stores dates as numbers explains the details.

One display quirk: the result cell sometimes takes on a date format and shows something like 12/5/1907. The number underneath is right. Change the cell's format to General or Number (Windows: Ctrl+Shift+~; or use Home > Number Format) to see the days.

How DAYS works#

DAYS does exactly what subtraction does, written as a function:

=DAYS(end_date, start_date)

The end date comes first, just like in =end-start. If you put the earlier date first, you get a negative number rather than an error. The main reason to use DAYS is readability: a colleague sees at once that the formula counts days between two dates. Our guide to days between two dates has more examples.

How DATEDIF works#

DATEDIF counts the gap between two dates in the unit you choose:

=DATEDIF(start_date, end_date, unit)

The start date comes first, the opposite of DAYS. The unit is a code in double quotes:

DATEDIF doesn't appear in Excel's autocomplete list, because Microsoft keeps it mainly for compatibility with older spreadsheets. It still works in every version; you just have to type the whole name. Microsoft's own documentation warns that the "MD" unit can give inaccurate results in some cases, so check any formula that relies on it. The DATEDIF function guide covers each unit.

The same dates, side by side#

Here is one employee's record. The task: how long between hire and last review?

Put the pieces together for a tenure label like 7 yrs, 11 mos:

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

For tenure up to today, replace C2 with TODAY(). Our guide on calculating age or years of service covers that version step by step.

What happens when a date is missing?#

Real lists often have gaps, such as an employee who hasn't had a review yet. The three methods fail in different ways when the end date is blank, because Excel treats an empty cell as 0, the very start of its date system:

None of these is useful in a report. Check for the blank first and show nothing instead:

=IF(C2="","",DATEDIF(B2,C2,"Y"))

The same pattern works with subtraction or DAYS. If you need a decimal number of years instead of whole ones, such as 7.93 years of service, use YEARFRAC; our guide to YEARFRAC in Excel explains its basis options.

When to use subtraction or DAYS#

Use subtraction or DAYS whenever the answer is a number of days. They are the better choice when:

• You count days until a deadline or days since an invoice was sent.
• You compare against a day limit, such as =IF(C2-B2>30,"Overdue","OK").
• You want a negative result to mean "in the future" rather than an error.
• The formula must be easy for anyone to read. Pick one style and use it across the workbook.

If the question is about working days, neither is right: weekends count as days. Use NETWORKDAYS instead, as explained in our comparison of NETWORKDAYS vs WORKDAY.

When to use DATEDIF#

Use DATEDIF when the answer must be in whole months or years. It is the better choice when:

• You calculate tenure or length of service for HR reports. This comes up constantly in Excel skills for HR and recruiting jobs.
• You calculate age from a date of birth.
• You need "X years, Y months" labels.
• You check eligibility rules, such as "at least 6 complete months of service".

Dividing days by 365 is a common shortcut, but it drifts because of leap years. For a decimal number of years, use YEARFRAC instead; for whole years, use DATEDIF.

Common mistakes with DAYS and DATEDIF#

These slips cause most wrong date results:

1. Swapping the argument order between functions. DAYS is end, start. DATEDIF is start, end. Read the formula aloud before you press Enter.
2. Forgetting the quotes around the unit. =DATEDIF(B2,C2,Y) gives #NAME?; it must be "Y".
3. Dates stored as text. A date typed as text, such as one imported from another system, may not calculate. Check that the cell is right-aligned by default, or test it with =ISNUMBER(B2).
4. Expecting an inclusive count. All three methods count the gap. Add 1 if both the first and last day should count.
5. Dividing by 365 for years. Use DATEDIF with "Y" for whole years.
6. Relying on "MD" without checking. Test it against a few dates you can count by hand.

Getting the argument order right is the same kind of detail that trips people up in our comparison of MATCH types 0, 1 and -1.

Our recommendation#

Use subtraction or DAYS for days, and DATEDIF for whole months and years. Subtraction is the shortest and works everywhere, while DAYS reads more clearly in a shared workbook; pick one and stay consistent. Reach for DATEDIF whenever the answer must be in complete months or years, and always put the earlier date first.

In short: days with =end-start or DAYS, months and years with DATEDIF.

Practise it#

Try the DATEDIF months exercise on this page, then open the days between dates lesson. It takes you from plain subtraction to DAYS, then DATEDIF with "Y", "M" and "YM", and finishes with a "7 yrs, 11 mos" tenure label built from the pieces.