Calculate date overlap in days: find shared days between two bookings or projects

One formula handles partial, nested, touching, and disjoint ranges

Testing all four ways two date intervals can overlap with nested IF statements is notoriously error-prone. Instead, note that the intersection of [Start1, End1] and [Start2, End2] always begins at MAX(Start1, Start2) and ends at MIN(End1, End2).

For inclusive calendar days (where June 15 through June 20 counts as 6 days), the overlap is MIN(End1, End2) - MAX(Start1, Start2) + 1—clamped at 0 with MAX(0, ...) whenever the two ranges do not touch.

Validate that each start is no later than its end and that inputs are date-only numeric serials for the inclusive-day calculation. The boolean test below treats touching endpoints as overlapping; use strict inequalities for half-open checkout/check-in intervals.

Inclusive vs hotel-night overlap

Use + 1 when both endpoints count as active days (6 days for Jun 15..Jun 20). Drop the + 1 for hotel nights or continuous timestamps where checkout on Jun 20 and check-in on Jun 20 have 0 nights of overlap.

=MAX(0, MIN(B2, D2) - MAX(A2, C2) + 1)

Boolean overlap flag for conflict detection

Testing (A2 <= D2) * (C2 <= B2) > 0 returns TRUE whenever two bookings collide on at least one day.

=AND(A2 <= D2, C2 <= B2)

Further reading

• Microsoft Support — MIN function