Overlapping Days Between Two Date Ranges

All versionsMIN/MAX

Two bookings, two projects, two leave requests — how many days do they overlap? A neat MIN/MAX trick finds the shared span without any nested IFs.

Quick formula: for range 1 in B1:C1 and range 2 in B2:C2 (start, end):

=MAX(0, MIN(C1,C2) - MAX(B1,B2) + 1)

The overlap runs from the later start to the earlier end; MAX(0,…) returns 0 when they don’t overlap at all.

Functions used (tap for the full reference guide):

MIN MAX

The example

Two stays that partly overlap.

	A	B	C
1	Range	Start	End
2	A	6/1/2026	6/10/2026
3	B	6/7/2026	6/15/2026
4	Overlap days	4	(Jun 7-10)

The formula

Days the two ranges share:

=MAX(0, MIN(C1,C2) - MAX(B1,B2) + 1) // Jun 7-10 inclusive = 4 days

How it works

The overlap is bounded by the latest start and the earliest end:

The overlap can’t start before either range does, so its start is MAX(B1,B2) — the later of the two starts.
It can’t end after either range ends, so its end is MIN(C1,C2) — the earlier of the two ends.
Subtract and add 1 (both endpoints count) to get the day count: MIN(C1,C2) - MAX(B1,B2) + 1.
If the ranges don’t touch, that result goes negative — MAX(0, …) clamps it to 0.

Do they overlap at all? Wrap it in a test: =IF(MAX(0,MIN(C1,C2)-MAX(B1,B2)+1)>0, "Overlap", "No overlap"). Drop the +1 if you want the gap in nights rather than inclusive days.

Try it: interactive demo

Live demo

Set two date ranges.

A start

A end

B start

B end

Overlap days:

Variations

Do they overlap?

Yes/no flag:

=IF(MAX(0,MIN(C1,C2)-MAX(B1,B2)+1)>0,"Yes","No")

Nights instead of days

Drop the +1:

=MAX(0, MIN(C1,C2) - MAX(B1,B2))

Gap when they don’t overlap

Days between the ranges:

=MAX(0, MAX(B1,B2) - MIN(C1,C2) - 1)

Pitfalls & errors

The +1 sets inclusivity. With +1 both endpoints count (calendar days); without it you get nights/full intervals. Pick the one your context needs.

Don’t skip MAX(0,…). Without it, non-overlapping ranges return a misleading negative number instead of 0.

Ends must be ≥ starts. The trick assumes each range’s end is on or after its start. Validate inputs if users might enter them backwards.

Practice workbook

📊

Download the free Overlapping Days Between Two Date Ranges practice workbook

An overlap calculator with the yes/no, nights, and gap variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I calculate overlapping days between two date ranges in Excel?

Use =MAX(0, MIN(end1,end2) - MAX(start1,start2) + 1). The overlap runs from the later start to the earlier end, and MAX(0,…) returns 0 when they don't overlap.

Why is there a +1 in the formula?

It makes both endpoints count, giving inclusive calendar days. Drop the +1 if you want nights or non-inclusive intervals.

How do I just check whether two ranges overlap?

Wrap it in IF: =IF(MAX(0,MIN(end1,end2)-MAX(start1,start2)+1)>0,"Yes","No").

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Working days (holidays) · Days until a date · Min if criteria

Function references: MIN · MAX