Two bookings use the same room in November. One runs from the 12th through the 17th, and the other from the 15th through the 20th. Find how many calendar dates both bookings include.

Use the later start and the earlier end

Enter the first start and end in B2 and C2, and the second in D2 and E2. Use real date values with no times, and check each start is on or before its end. Enter:

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

The +1 includes both endpoints. MAX(0,...) returns zero when the ranges do not intersect.

Two sample bookings run 12–17 November and 15–20 November, with 15–17 highlighted as shared days.

Illustrated overlap. Use the later start and earlier end.

Test the boundaries

With 12–17 and 15–20 November, the shared dates are 15, 16 and 17, so the result is 3. Change the second start to the 17th to check that it returns 1. Change it to the 18th to check zero. Display the result as a number.

Three boundary checks show a three-day overlap, a shared endpoint worth one day and a disjoint result of zero.

Illustrated formula tests. Count a shared calendar date once.

Use the booking system’s own endpoint rule if the end date represents checkout and should be excluded.