Excel Formula for Days Between Dates: A Practical Guide
Master calculating days between dates in Excel using DATEDIF, subtraction, and workday formulas with clear examples, error handling, and practical tips from XLS Library.
The quickest way to compute days between two dates is with DATEDIF or simple subtraction. Use =DATEDIF(A2,B2,"d") to count days, or =B2-A2 for a direct difference. To count inclusively add +1, and for workdays use NETWORKDAYS. This quick guide shows practical formulas and edge-case handling.
Introduction: Why accurate days-between calculations matter
According to XLS Library, getting the number of days between two dates right is a common source of errors in spreadsheets. Misinterpreting dates, including or excluding endpoints, or mixing text dates with real dates leads to incorrect results that ripple through dashboards and timelines. In this guide, we unpack reliable Excel formulas for calculating days between dates, plus practical variations for inclusive counting, workdays, and error handling. The goal is to give you transparent, reusable formulas you can drop into real workbooks with confidence.
=DATEDIF(A2,B2,"d") -- basic day count (end date minus start date)
=B2-A2 -- direct difference, requires date-formatted cellsWhether you’re planning a project timeline or tracking service days, these techniques scale across worksheets and teams.
contextEnabled":false} ,{
Core formulas to compute days between dates
Excel provides several ways to measure the interval between two dates. The classic approach is DATEDIF with the "d" unit, which returns the exact number of days between two dates. A simple subtraction (end_date - start_date) yields the same numeric result when both cells are proper dates. Always ensure your inputs are numeric dates; otherwise, you may see #VALUE! or incorrect results.
=DATEDIF(A2,B2,"d") -- days between, ignoring time portion
=B2-A2 -- direct difference; works if A2 and B2 are real dates
=IF(B2>=A2,B2-A2,A2-B2) -- safe when dates might be reversedWhy use DATEDIF? It’s explicit about units and generally clearer for readers. If you’re working with large data ranges, the subtraction form can be a tiny bit faster, but both are reliable for date arithmetic. In complex workbooks, combining with LET can improve readability and performance.
contextEnabled":false} ,{
Inclusive vs exclusive counting: counting the start and end dates
Different scenarios require inclusive or exclusive counting. To count days inclusively (including both start and end dates), add 1 to the day difference. If you need exclusive counting, keep the simple difference. Additionally, for business days only, NETWORKDAYS counts weekdays and can incorporate holidays.
// Inclusive counting
=DATEDIF(A2,B2,"d")+1
// Exclusive counting (same as plain difference when dates are valid)
=DATEDIF(A2,B2,"d")
// Business days between dates, inclusive of both ends
=NETWORKDAYS(A2,B2, Holidays!A2:A10)If your data includes weekend-only ranges or custom weekends, consider NETWORKDAYS.INTL with a weekend pattern argument.
contextEnabled":false} ,{
Handling date formats and common errors
Dates can be stored as text or numbers. When dates are text, Excel’s arithmetic can fail. Convert text to dates with DATEVALUE, or validate with ISNUMBER. Always normalize date inputs in your workbook to avoid subtle errors that appear only after aggregation.
// Convert text to date
=DATEVALUE("2026-04-15")
// Validate input before calculating
=IF(AND(ISNUMBER(A2),ISNUMBER(B2)), B2-A2, "invalid date")
// Normalize mixed formats in a column (drag down)
=DATEVALUE(TEXT(A2,"yyyy-mm-dd"))Pro tip: Use consistent regional date formats to minimize parsing issues when sharing workbooks.
contextEnabled":false} ,{
Practical examples in a workbook
Consider a simple table where A2 contains a start date and B2 contains an end date. The following examples illustrate common tasks you’ll encounter in daily work:
// Example 1: Basic day count
=DATEDIF(A2,B2,"d")
// Example 2: Inclusive day count for durations
=DATEDIF(A2,B2,"d")+1
// Example 3: Workdays between with holidays (Mon-Fri)
=NETWORKDAYS(A2,B2, Holidays!A2:A10)These patterns scale when you copy formulas down a column and reference corresponding rows. When dates are far apart across leap years, DATEDIF handles leap day correctly, assuming valid dates.
contextEnabled":false} ,{
Performance considerations and alternatives
For large spreadsheets, formula complexity can impact recalculation time. Prefer simple subtraction when you don’t need unit specificity, and use DATEDIF when you want explicit day counts. You can optimize with the LET function to assign names to repeated expressions, reducing recomputation.
=LET(s,A2,e,B2,IF(e>=s,e-s,s-e))If you’re generating many date differences in Power Query or Excel’s dynamic arrays, consider moving heavy lifting to Power Query for preprocessing, then load results back into the worksheet.
contextEnabled":false} ,{
Common variations and edge cases
Edge cases include identical dates, reversed date order, and time components within dates. If time is present, you may want to strip time before counting days or round down with INT.
// Remove time component before counting days
=DATEDIF(INT(A2),INT(B2),"d")
// Always get a positive difference (absolute value)
=ABS(B2-A2)Besides, when every day matters, consider using: =IF(B2>=A2,B2-A2,A2-B2) to ensure a positive result regardless of order.
contextEnabled":false} ,{
Putting it all together: a reusable template for your workbook
This final section provides a compact template you can adapt for various sheets. It demonstrates best practices: clear headers, date normalization, and documented formulas. Save this as a small snippet you can copy-paste into new workbooks and adjust for holidays, inclusive counting, or business-day calculations.
// Template: basic and inclusive options
StartDate EndDate DaysBetween InclusiveDays Workdays
2026-04-01 2026-04-10 =DATEDIF(A2,B2,"d") =DATEDIF(A2,B2,"d")+1 =NETWORKDAYS(A2,B2, Holidays!A2:A10)By keeping inputs clean and formulas well-documented, you’ll reduce maintenance time and improve accuracy across teams.
Steps
Estimated time: 20-40 minutes
- 1
Prepare date columns
Ensure your worksheet has a start date column (A) and an end date column (B). Convert text dates to real dates if needed, and set a consistent date format across the sheet.
Tip: Use Data > Text to Columns or DATEVALUE to normalize dates if you suspect text entries. - 2
Choose the calculation method
Decide whether you need a simple day count, inclusive counting, or workday counts. This choice determines whether you use DATEDIF, +1, or NETWORKDAYS in your formulas.
Tip: For readability, add a header row and label each column with the exact formula purpose. - 3
Apply the formulas
Enter the chosen formula in the DaysBetween column and copy down. Validate a few rows to confirm correctness.
Tip: Check edge cases: same dates, start date after end date, and leap year scenarios. - 4
Add error handling
Wrap formulas with IF(ISNUMBER(..)) or IFERROR to catch non-dates or misformatted data.
Tip: Prevent silent errors by displaying a clear message like "invalid date". - 5
Test and document
Create a small test set with known outcomes, and add comments in the sheet explaining each formula.
Tip: Document assumptions, such as inclusive vs exclusive counting and holidays.
Prerequisites
Required
- Required
- Knowledge of basic Excel functions (DATE, DATEDIF, NETWORKDAYS)Required
- A workbook with date columns (start_date, end_date)Required
- Stable date formats (YYYY-MM-DD or locale-appropriate)Required
Optional
- Optionally, a Holidays sheet for NETWORKDAYSOptional
Keyboard Shortcuts
| Action | Shortcut |
|---|---|
| Copy formulaCopy a selected formula from a cell | Ctrl+C |
| Paste formulaPaste into target cells or formulas | Ctrl+V |
| Fill downCopy the formula down a column | Ctrl+D |
| UndoRevert the last edit | Ctrl+Z |
| Enter formulaConfirm input in a cell | ↵ |
| Move to next cellNavigate across cells while editing | ⇥ |
People Also Ask
What is the difference between DATEDIF and simple subtraction for days between dates?
DATEDIF explicitly counts days between two dates and is generally clearer to read. Subtraction (end-start) yields the same numeric result when both inputs are proper dates. Choose based on readability and worksheet consistency.
DATEDIF is clearer for days between dates; subtraction works too if dates are guaranteed to be dates.
Can I count weekends or holidays when calculating days between dates?
Yes. Use NETWORKDAYS to count workdays between two dates and pass a holidays range to exclude holidays. For weekends with custom patterns, NETWORKDAYS.INTL offers flexible weekend definitions.
NETWORKDAYS helps you count only workdays, excluding holidays if you list them.
How do I handle dates in different formats?
Convert any non-date text to dates using DATEVALUE or VALUE, and ensure consistent regional formats. Use ISNUMBER to confirm dates before calculating differences.
Make sure your dates are real Excel dates before calculating days.
How can I count days between dates inclusively in a table?
Add 1 to the difference when you want to include both start and end dates. Eg: =DATEDIF(A2,B2,"d")+1. This is common for durations spanning both endpoints.
Add one to include both dates in the count.
Is NETWORKDAYS suitable for non-business days?
NETWORKDAYS counts weekdays by default. It’s great for business-day calculations, but for general days-between use DATEDIF or subtraction. Holidays can be excluded with an optional range.
NETWORKDAYS is for workdays, not calendar days.
What happens if end date is before start date?
If end date is before start date, the simple difference yields a negative number. Use ABS or swap the dates, depending on your intent.
If dates are reversed, take the absolute value or switch them.
The Essentials
- Use DATEDIF for clear day counts
- Add +1 for inclusive counting when needed
- Use NETWORKDAYS for workday calculations
- Validate inputs to prevent date-format errors