Two dates in two cells, and a third cell that just needs to show "how many days apart."
It looks like a one-key answer, until =B2-A2 comes back showing another date
like 1/17/1900 instead of a number, or the count includes weekends that shouldn't count
toward a deadline, or a delivery SLA report quietly includes the two public holidays that
fell in between.
Excel actually handles date differences well once you know which formula fits the question you're asking — total calendar days, workdays only, or a full years/months/days breakdown. This guide covers each one, the mistakes that break them, and which to use when.
The simplest way to find the difference between two dates in Excel is direct subtraction,
=B2-A2, which returns the number of calendar days as long as the result cell
is formatted as a number. To count only workdays, use
=NETWORKDAYS(A2,B2), which automatically excludes weekends and, optionally,
a list of holiday dates.
What is a date difference formula, and what are the options?
"Date difference" can mean three different things depending on what the spreadsheet needs to answer, and Excel has a separate tool for each one.
- Direct subtraction (
=B2-A2) returns the total number of calendar days between two dates, counting every day including weekends and holidays. - NETWORKDAYS counts only working days between two dates, automatically skipping Saturdays and Sundays, with an optional list of holidays to exclude as well.
- DATEDIF breaks a span down into completed years, months, and days rather than a single day count, which is closer to how a person would describe a duration out loud.
All three read the same two date cells; the difference is entirely in how the result gets expressed and which days get counted along the way.
Why the right formula matters
Picking the wrong formula for the question doesn't throw an error — it just quietly gives a technically-correct number that answers something else. That shows up in a few common places:
- SLA and deadline tracking. A support or delivery SLA measured "in business days" will look breached or missed if the report is actually counting calendar days, weekends included.
- Payroll and timesheets. Hours or days worked need workdays, not calendar span, or every pay period will overstate the time an employee was actually on the clock.
- Project timelines. A task duration that includes weekends by accident can make a schedule look tighter, or looser, than the plan actually is.
- Invoice and contract terms. "Net 30" and similar terms depend on counting calendar days consistently, so a formatting slip that turns the result into a date instead of a number can go unnoticed until a due-date report is wrong.
Step-by-step: calculating date differences in Excel
Method 1: Total calendar days (direct subtraction)
- Enter both dates in separate cells. Make sure both are stored as real dates, not text — for example, A2 as the start date and B2 as the end date.
- Click an empty cell for the result. This is where the day count will appear.
-
Type the subtraction formula.
=B2-A2
- Format the result cell as Number. If the cell shows a date instead of a count, change its format to Number or General so it displays the day total correctly.
Method 2: Workdays only (NETWORKDAYS)
-
Use NETWORKDAYS with the start and end dates.
=NETWORKDAYS(A2,B2)
-
Add a holiday range if needed. A third, optional argument excludes specific dates, such as public holidays, from the count as well.
=NETWORKDAYS(A2,B2,D2:D10)
- Press Enter. The result is the number of working days between the two dates, weekends and listed holidays excluded.
Method 3: Difference in years, months, and days
-
Use three DATEDIF calls in one formula, changing only the last argument for years, months, and days.
=DATEDIF(A2,B2,"Y")&" years, "&DATEDIF(A2,B2,"YM")&" months, "&DATEDIF(A2,B2,"MD")&" days"
- Press Enter. The formula concatenates all three results into a readable duration, like "1 year, 4 months, 12 days."
Common mistakes with date difference formulas
1. Forgetting to reformat the result cell
Subtracting two dates often inherits Date formatting from one of the source cells, so Excel displays the day count as a date serial, like 1/17/1900, instead of a plain number such as 17.
2. Using subtraction when only workdays should count
=B2-A2 counts every day in between, weekends included, which overstates a
deadline, SLA, or timesheet calculation that's actually meant to track business days only.
3. Storing one of the dates as text
A date typed or imported in a format Excel doesn't recognize is stored as text, and both subtraction and NETWORKDAYS return a #VALUE! error against a text cell instead of calculating a difference.
4. Reversing the start and end dates
Putting the later date first returns a negative number from subtraction, and it returns a #NUM! error from DATEDIF, since DATEDIF expects the earlier date as its first argument.
DATEVALUE(), before running the formula again.
Real-world formula examples
A few common scenarios and the formula each one needs.
Subtraction vs NETWORKDAYS vs DATEDIF
A side-by-side look at how the three approaches compare in Excel.
| Factor | Direct subtraction | NETWORKDAYS | DATEDIF |
|---|---|---|---|
| Output | Total calendar days | Working days only | Years, months, or days |
| Excludes weekends | No | Yes | No |
| Can exclude holidays | No | Yes, optional argument | No |
| Best for | Contract terms, invoice due dates | SLAs, payroll, project workdays | Tenure, age, human-readable durations |
Skip the spreadsheet: free date difference calculator
If you just need the gap between two dates and don't want to open Excel, the Rebrixe Date Difference Calculator works entirely in your browser: enter a start and end date, get the exact difference in days, weeks, months, or years instantly. No formulas, no account, no data sent anywhere.