A shared endpoint counts once
Subtracting dates without +1 loses the inclusive day, including a same-date interval.
Count dates shared by two inclusive intervals
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 number of calendar dates shared by each A:B interval and the inclusive reporting window G2:G3. Count both endpoints, return 0 for no overlap, and return Review when a source end precedes its start. Dates are whole numeric serials from 2000 through 2099; G3 must be on or after G2.
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
Clip each original interval to a reporting window, count both endpoints and preserve a distinct review result for reversed records.
THE QUICK ANSWER01
Clip each original interval to a reporting window, count both endpoints and preserve a distinct review result for reversed records.
ƒx
=IF(B2<A2,"Review",MAX(0,MIN(B2,$G$3)-MAX(A2,$G$2)+1))For the example data
C2 3C3 1C4 0C5 Review
The formula uses the interactive worksheet above. Adjust ranges and criteria for your own data.
How it works
Calendar dates are not working dates
This task counts every shared date, including weekends and holidays. A workday calendar would require an additional explicit eligibility rule.
Inclusive versus half-open intervals
Both source and reporting intervals include their endpoints. A pair touching on one date overlaps for one day. Do not apply this formula unchanged to time intervals or half-open booking boundaries.
Read the full step-by-step walkthrough
- Validate the source direction
A reversed record is an issue to review. Sorting its endpoints silently changes the meaning of the source.
- Use the later start
The overlap cannot begin before either interval begins. MAX selects the later of the source start and fixed reporting start.
- Use the earlier end
The overlap cannot continue beyond either interval. MIN selects the earlier source or reporting end.
- Include both dates and floor disjoint windows
A same-day overlap counts one. Adding one handles inclusive endpoints; MAX with zero prevents a disjoint pair from producing a negative count.
- Lock the window while filling
Each row changes its own source interval. Keep the report window fixed in G2:G3 when filling down.
Things to watch
Reversing a record is not a repair
MIN/MAX across all four values can hide a reversed source. Return Review instead.
About this example
Formula entry, fill and all six authored datasets were checked in Chrome on October 18, 2026. Numeric timestamp/clock display and original/current/frozen downloads have separate package/browser checks. Local clocks carry no timezone or daylight-saving interpretation; no payroll policy is inferred. Native Excel scope is recorded in the date/time validation report; Google Sheets remains unverified.
Content version 1. Practice checks compare calculated results across different datasets; they do not certify every possible formula.