Excel date difference formula: DAYS, DATEDIF and NETWORKDAYS
Choose an Excel date difference formula by the result you need: subtraction for elapsed calendar days, DAYS for a clear date difference, DATEDIF for years, months, and days, and NETWORKDAYS for weekdays. This guide uses A2 as the start date and B2 as the end date.
On this page
- Start with the counting rule
- Prepare real date cells
- Calculate the basic date difference
- Include the start and end dates
- Show years, months, and days with DATEDIF
- Calculate days through today
- Exclude weekends and holidays
- Troubleshoot results that look wrong
- Check a formula with the online calculator
- FAQ
Start with the counting rule
Define the result before choosing a function. Use =B2-A2 for elapsed calendar days, =B2-A2+1 when both dates count, NETWORKDAYS for weekdays, and DATEDIF for a years-months-days display.
The examples use A2 for the start date and B2 for the end date; replace them with your worksheet’s cells.
| What you need | Copy-ready formula | Counting rule or note |
|---|---|---|
| Elapsed days between two dates | =B2-A2 |
April 1 to April 10 returns 9 |
| An explicit date-difference function | =DAYS(B2,A2) |
End date first, start date second |
| Both dates included | =B2-A2+1 |
Use for inclusive calendar counts |
| Years, months, and days | =DATEDIF(A2,B2,"y") and related units |
Split the period into complete units |
| Days through today | =TODAY()-A2 |
Changes when Excel recalculates |
| Weekdays excluding holidays | =NETWORKDAYS(A2,B2,$F$2:$F$20) |
Use a date-only holiday range |
Three checks before you write the formula
- Are you counting elapsed time, or are both calendar endpoints counted?
- Should weekends only be removed, or should holidays and company closures be removed too?
- Should the answer be a number of days, or a readable years-months-days period?
Prepare real date cells
If #VALUE! appears, A2 or B2 may be text rather than a real date. Confirm the Date format, then test =B2-A2 before changing functions. Formatting alone may not convert imported text.
Imported values, apostrophes, and strings such as “April 1, 2026” can behave differently by region. Build a date explicitly with =DATE(2026,4,1). If cells contain times, =INT(B2)-INT(A2) removes the fractional part.
If subtraction returns an unexpected serial value or an error, fix the cell type and input format first. See Microsoft’s DATE function documentation for argument order.
Calculate the basic difference: subtraction and DAYS
With A2 as the start date and B2 as the end date, =B2-A2 is the simplest formula. April 1 to April 10 returns 9 because the start date is not counted as day one.
Use =DAYS(B2,A2) when the function name should make the operation clear. Its order is end date first, start date second; reversing them returns a negative value. Use =ABS(B2-A2) only when input order truly does not matter, because a negative result can warn about bad payroll or billing data.
Subtraction fits elapsed calendar days, project duration, and contract age when weekends remain in the count. For trips or events, decide whether the first date is a counted day before adding +1.
Remember the DAYS argument order
The general form is =DAYS(end_date,start_date). With A2 and B2, use =DAYS(B2,A2). A warning such as =IF(B2<A2,"Check date order",DAYS(B2,A2)) makes reversed rows easier to spot.
Include both dates: why +1 is needed
If “April 1 through April 10” counts every listed date, use =B2-A2+1. The ordinary difference is 9; the inclusive count is 10. The +1 is an arithmetic adjustment, not a special DATEDIF option.
Some policies count only one endpoint or neither. Do not add +1 automatically for contracts, leave, coverage, or billing. Record the rule in the worksheet so the formula remains understandable.
Compare endpoint rules with the site’s inclusive day-count calculator before finalizing the formula.
Example: April 1 through April 10
- Elapsed difference:
=B2-A2→ 9 days - Both endpoints included:
=B2-A2+1→ 10 days - Same start and end date: difference is 0 days; inclusive count is 1 day
Show years, months, and days with DATEDIF
For a period such as “2 years, 3 months, 12 days,” combine DATEDIF units: "y" for complete years, "ym" for remaining months, and "md" for remaining days.
Join the parts with & to return one cell: =DATEDIF(A2,B2,"y")&" years, "&DATEDIF(A2,B2,"ym")&" months, "&DATEDIF(A2,B2,"md")&" days". Use IF when zero-value units should be hidden.
DATEDIF errors when the start date follows the end date. Month-end and leap-year periods also differ from total days divided by 30, so keep the total day count for auditing. See Microsoft’s DATEDIF documentation.
How to read month-end results
January 31 to February 28 and leap-year periods cross months of different lengths, so DATEDIF can differ from total days divided by 30. Keep both the readable period and total days for reproducible checks.
Calculate days through today with TODAY
If A2 is the start date, =TODAY()-A2 returns elapsed days through today. Use =TODAY()-A2+1 when the start date counts as day one.
TODAY returns the date at recalculation, so the value changes over time. For a frozen report, paste a value or reference a saved date. A future start date produces a negative result, which a conditional formula can hide.
Leave a future date blank
=IF(A2>TODAY(),"",TODAY()-A2)- To count the start date, change the final expression to
TODAY()-A2+1. - For a fixed comparison date, reference a saved date cell instead of TODAY.
Exclude weekends and holidays with NETWORKDAYS
For weekdays rather than calendar days, use =NETWORKDAYS(A2,B2). The standard form excludes Saturday and Sunday and returns the working-day count.
To exclude holidays or company closures, list dates in F2:F20 and use =NETWORKDAYS(A2,B2,$F$2:$F$20). Absolute references keep the range fixed; keep notes out of the holiday list.
For a different weekend pattern, use NETWORKDAYS.INTL. To find a date N business days after another date, use WORKDAY; NETWORKDAYS returns a count.
Date difference and business-day count are different
=B2-A2 counts calendar days; =NETWORKDAYS(A2,B2) counts qualifying workdays. Label columns clearly so the definitions are not mixed. The site’s weekday, weekend, and holiday calculator shows the breakdown.
Troubleshoot results that look wrong
Most errors come from cell types, reversed input, endpoint rules, or an incomplete holiday range. Use this table before rebuilding the worksheet.
| Symptom | Likely cause | Check or fix |
|---|---|---|
| The result is #VALUE! | A date is text or invalid | Rebuild the value with DATE and check the number format |
| The result is one day short | Elapsed and inclusive counts were mixed | Compare =B2-A2 with =B2-A2+1 |
| The result is negative | The end date is before the start date | Correct the order; use ABS only when order does not matter |
| DATEDIF returns an error | Invalid dates or start date after end date | Confirm both cells are dates and A2 is on or before B2 |
| Business-day count is too high | The holiday range was omitted | Add a date-only range as NETWORKDAYS’s third argument |
| The answer changes each day | TODAY is part of the formula | Paste a value or save a reference date |
Check the formula with an online calculator
After writing a formula, use the site’s Excel date calculator to compare ordinary, inclusive, business-day, and today-based results.
Treat the online result as a check, not a replacement for a policy or contract. Use the date difference calculator for raw date differences and the business-day calculator for workday counts.
Calculate days nowSummary: define the count before choosing the formula
In short, =B2-A2 gives elapsed days, +1 makes the count inclusive, DAYS states the difference, DATEDIF splits years and months, TODAY uses the current date, and NETWORKDAYS excludes weekends and holidays.
When unsure, calculate both ordinary and inclusive results, record the rule, and compare the worksheet with the site’s Excel date calculator.
Excel date difference formula FAQ
What is the simplest Excel date difference formula?
Put the start date in A2 and the end date in B2, then use =B2-A2. This returns elapsed days. Use =B2-A2+1 when both calendar endpoints should count.
How do I include the start date in an Excel day count?
Add one to the ordinary difference: =B2-A2+1. This treats the start and end dates as counted calendar days.
What is the correct order for the DAYS function?
Use =DAYS(end_date,start_date). With A2 as the start date and B2 as the end date, the formula is =DAYS(B2,A2).
Which Excel function shows years, months, and days?
Combine DATEDIF units: "y" for complete years, "ym" for remaining months, and "md" for remaining days.
How can I exclude weekends and holidays?
Use NETWORKDAYS. =NETWORKDAYS(A2,B2) removes Saturday and Sunday; add a holiday date range as the third argument to remove listed holidays too.
Why is my Excel date calculation off by one day?
The formula may be using elapsed difference while the task expects both endpoints to count. Compare =B2-A2 with =B2-A2+1 and document the chosen rule.
Check the official function specifications
Use Microsoft’s official support pages when you need to verify an argument order or date-unit behavior.