Excel Days Between Dates Formula: Quick Guide
Master the excel days between dates formula with subtraction, DATEDIF, and NETWORKDAYS. Learn inclusive vs exclusive counting, workday calculations, holidays, and practical worksheet examples to avoid common date errors in 2026.
Excel provides several reliable ways to compute the days between two dates. The simplest method is direct subtraction: =EndDate-StartDate. For inclusive counting, add 1. Use DATEDIF for a simple day difference, or NETWORKDAYS to count workdays only. This guide covers syntax, edge cases, and practical worksheets so you can master date arithmetic in Excel.
Overview of date difference in Excel
Calculating the difference between two dates in Excel yields a numeric day count. The core techniques are direct subtraction, DATEDIF, and NETWORKDAYS. Understanding when to use each function helps you handle time components correctly and adapt to holidays. According to XLS Library, mastering date arithmetic reduces errors in reporting and speeds up real-world data tasks. In practice, you often start with a simple subtraction and then refine for inclusivity or workdays.
=B2-A2The result represents the number of calendar days between StartDate (A2) and EndDate (B2). If EndDate precedes StartDate, you’ll get a negative value, which is useful for identifying reversed dates but may require correction in reports.
Variations:
- Inclusive counting: =B2-A2+1
- Negative results handling: =IF(B2>=A2,B2-A2,
Steps
Estimated time: 30-45 minutes
- 1
Prepare dates in separate columns
Create two columns with dates in proper date format, e.g., StartDate in A2 and EndDate in B2. Ensure cells are recognized as dates (not text).
Tip: Use a helper column to verify data types by formatting as General. - 2
Choose your counting mode
Decide whether you want calendar days, inclusive days, or workdays. This decision drives the formula you’ll use.
Tip: Document the rule in a note on the sheet for future users. - 3
Try simple subtraction for calendar days
Enter =B2-A2 in a helper cell to get the days between dates. This is the fastest method for straightforward calendar day counts.
Tip: Disable automatic decimal formatting to see whole-day results. - 4
Add 1 for inclusive counting
If you want to count both start and end dates, use =B2-A2+1. This is common for duration calculations.
Tip: Be mindful of holidays not being counted in this method. - 5
Compute workdays with NETWORKDAYS
Use =NETWORKDAYS(A2,B2) to count only weekdays, excluding weekends. You can add a holidays range.
Tip: Supply a Holidays range if you want to skip observed holidays. - 6
Handle times attached to dates
If times are present, wrap dates with INT to ignore time components: =INT(B2)-INT(A2) or use =DATEDIF(A2,B2,"d").
Tip: INT removes the fractional day portion before subtraction. - 7
Guard against reversed dates
If EndDate is earlier than StartDate, you’ll get a negative value. Use IF or ABS to handle gracefully.
Tip: Example: =IF(B2>=A2,B2-A2, A2-B2) - 8
Create a reusable worksheet pattern
Convert your formulas into a named range or a small template so you can drag them across rows and preserve consistency.
Tip: Document the template so others can reuse it.
Prerequisites
Required
- Required
- Date formatting basics (YYYY-MM-DD)Required
- Familiarity with Excel formula syntax and cell referencesRequired
Optional
- Optional: Sample dataset with dates for practiceOptional
Keyboard Shortcuts
| Action | Shortcut |
|---|---|
| CopyCopy selected cells | Ctrl+C |
| PastePaste into target cells | Ctrl+V |
| Open Format CellsFormat cells dialog | Ctrl+1 |
People Also Ask
What is the simplest way to count days between two dates?
The simplest method is to subtract the start date from the end date: =EndDate-StartDate. This yields the calendar-day difference. For inclusive counting, add 1. If you need workdays, switch to NETWORKDAYS.
Subtract the start date from the end date to get calendar days. Add one for inclusion, or use NETWORKDAYS for workdays.
How do I count workdays between dates and exclude weekends?
Use NETWORKDAYS(StartDate,EndDate) to count weekdays. If you have holidays, include them as a range: NETWORKDAYS(StartDate,EndDate, Holidays). For custom weekends, use NETWORKDAYS.INTL.
Use NETWORKDAYS with an optional holidays list to count only workdays.
How can I include the end date in the count?
To include the end date, use =EndDate-StartDate+1. This simple adjustment makes the range inclusive for most schedules.
Add one to the difference to include the end date.
What if the end date is earlier than the start date?
If EndDate is before StartDate, subtraction yields a negative number. Use IF to enforce non-negative results or ABS to get absolute days.
If dates are reversed, handle with IF or ABS to avoid negative results.
Are there differences between Windows and Mac Excel for date calculations?
Most date formulas are consistent, but the underlying date system can differ. Ensure your workbook uses the same base and validate results across platforms.
Check that both platforms use the same date system to avoid mismatches.
Can I count holidays in the days-between calculation?
Yes. Use NETWORKDAYS(StartDate,EndDate, Holidays) or NETWORKDAYS.INTL with a specified weekend pattern and holidays list.
Include a holidays range to skip specific non-working days.
The Essentials
- Use subtraction for quick calendar-day counts
- NETWORKDAYS counts workdays and supports holidays
- DATEDIF offers an explicit day-difference option
- NETWORKDAYS.INTL allows custom weekend definitions