Subtracting dates gives a calendar difference
B2-A2 does not count eligible dates and excludes the inclusive endpoint rule.
Count eligible dates between two endpoints
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
Fill C2:C5 with the signed number of eligible dates from column A through column B, including both endpoints when they are working dates. Apply the text weekend mask in G2 and closure dates G4:G7. Reversing the interval reverses the sign; an all-closed weekly mask returns zero.
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: C2, C3, C4, C5. Browser calculations may differ from your Excel version.
Selection & formula search
Practice stays on this device. No worksheet data is uploaded.
Notes & examples
About this lesson
Count both endpoints when they are working dates, preserve the sign of reversed intervals, and avoid double-counting closures.
THE QUICK ANSWER01
NETWORKDAYS.INTL counts an inclusive date interval after removing weekly closures and holiday dates. It returns a negative count when the start follows the end.
ƒx
=NETWORKDAYS.INTL(A2,B2,$G$2,$G$4:$G$7)For the example data
C2 2C3 -2C4 0C5 0
The formula uses the interactive worksheet above. Adjust ranges and criteria for your own data.
How it works
A duration counter is not an offset calculator
NETWORKDAYS.INTL counts eligible dates including both endpoints. WORKDAY.INTL moves away from a start. Advancing one workday and counting the inclusive interval can therefore produce two working dates.
Accepted dates and range size
Use whole numeric dates from 2000-01-01 through 2099-12-31 and intervals of at most 3,660 calendar days. The seven-character text mask may close every weekday. Closure cells may be blank or whole numeric dates; no jurisdiction holiday rules are assumed.
Read the full step-by-step walkthrough
- Read both endpoints
Eligible start and end dates both count. A one-date interval can therefore return one, while a weekend-only interval can return zero.
- Declare the weekly calendar
Use the same Monday-first text convention as the deadline task. NETWORKDAYS.INTL also accepts 1111111 and counts no eligible dates.
- Remove closure dates without double subtraction
The closure list is a set of excluded dates. Duplicate entries and a closure already on a weekly closed day do not remove additional dates.
- Preserve the interval sign
Do not sort the endpoints or wrap the result in ABS. The reversed case deliberately asks for a signed count.
- Fill and check boundary cases
Lock the calendar references, fill C2 down, and check one-day, reversed, all-closed and leap-day intervals.
Things to watch
An absolute count loses direction
ABS hides a reversed interval. This task explicitly retains its sign.
Subtracting every holiday row overcounts exclusions
A duplicate closure or a weekend holiday must not reduce the count twice.
About this example
Formula entry, filling, all authored datasets, walkthrough controls and original/current/frozen XLSX downloads were checked in Chrome on October 17, 2026. Dates remain numeric Excel 1900-system serials with readable formatting. Closure dates are invented office-calendar examples. Native Excel and Google Sheets recalculation remain unverified.
Content version 1. Practice checks compare calculated results across different datasets; they do not certify every possible formula.