How to Check for Overlapping Date Ranges in Excel

To check for overlapping date ranges in Excel, compare each range's start with the other's end. Two ranges overlap when each one starts on or before the day the other one ends, which is one short AND formula for a pair and one COUNTIFS formula for a whole list. This guide is for anyone who manages leave, bookings, shifts or project dates and needs to spot clashes before they cause trouble.

The rule for overlapping date ranges#

Two date ranges overlap when Start1 <= End2 and Start2 <= End1. Both must be true.

It is easier to see why by thinking about when ranges don't overlap. There are only two ways:

• Range 1 ends before range 2 starts (End1 < Start2), or
• Range 2 ends before range 1 starts (End2 < Start1).

If neither of these is true, the ranges share at least one day. Turning those two "no overlap" cases around gives the rule above. You don't need to check whether one range sits inside the other, because the rule already covers it.

All examples use this leave list in A1:C7:

The dates must be real Excel dates, not text. Excel stores a date as a number, which is why <= and >= work on them.

Check if two date ranges overlap with AND#

To compare two rows, put both comparisons inside AND:

=AND(B2<=C4,B4<=C2)

This compares Ana's two leave periods. 6 Jul is on or before 14 Jul, and 9 Jul is on or before 10 Jul, so the result is TRUE. The AND function guide explains how AND returns TRUE only when every test is true, and the guide to AND, OR and NOT shows more examples.

Wrap it in IF for a friendlier label:

=IF(AND(B2<=C4,B4<=C2),"Overlap","OK")

Find every overlap in a list with COUNTIFS#

To check a whole list, count how many rows clash with each row. In D2, type:

=COUNTIFS($B$2:$B$7,"<="&C2,$C$2:$C$7,">="&B2)-1

Fill it down to D7 (Windows: Ctrl+D; Mac: ⌘D). Here is what each part does:

• $B$2:$B$7,"<="&C2 keeps rows that start on or before this row's end.
• $C$2:$C$7,">="&B2 keeps rows that end on or after this row's start.
• -1 removes the row itself, because every range overlaps itself.

The & joins the operator to the date in the cell. Writing "<=C2" would compare against the text "C2" and return the wrong count. The COUNTIFS function guide covers this pattern, and COUNTIFS with a date range shows more date criteria.

For row 2, the result is 2: Ben's 8–9 Jul and Ana's own 9–14 Jul both overlap Ana's 6–10 Jul. That is a team view: it shows who is away at the same time.

Check overlaps for the same person or room only#

Often you only care about clashes within one group, such as the same employee or the same meeting room. Add a criteria pair for the group:

=COUNTIFS($A$2:$A$7,A2,$B$2:$B$7,"<="&C2,$C$2:$C$7,">="&B2)-1

Now row 2 returns 1: only Ana's second period clashes with her first. Filled down, the results are 1, 0, 1, 1, 0, 1. Ana has a double-booked request, and so does Chris, whose two periods both include 17 Jul.

To show a clear flag, wrap it: =IF(COUNTIFS(...)-1>0,"Clash",""). This is the check a leave tracker or an appointment tracker needs before a new booking is approved.

Count the overlapping days#

To know how many days two ranges share, take the later start and the earlier end:

=MAX(0,MIN(C2,C4)-MAX(B2,B4)+1)

For Ana's two periods, MIN(10 Jul, 14 Jul) is 10 Jul and MAX(6 Jul, 9 Jul) is 9 Jul. The difference is 1, plus 1 to count both days, so the result is 2 days (9 and 10 Jul). If the ranges don't overlap, the subtraction is negative and MAX(0,…) returns 0.

The same idea counts how many days of one range fall inside a fixed period, such as a month. If G1 holds 1 Jul 2026 and G2 holds 31 Jul 2026, then =MAX(0,MIN(C2,$G$2)-MAX(B2,$G$1)+1) returns the July days in each leave request. For working days only, use =NETWORKDAYS(MAX(B2,B4),MIN(C2,C4)), which returns 2 here too. Wrap it in MAX(0,…) because NETWORKDAYS returns a negative number when there is no overlap.

Should touching ranges count as an overlap?#

With <= and >=, ranges that touch on one day count as overlapping. Chris's leave ends on 17 Jul and his next request starts on 17 Jul, so they overlap by one day.

That is right for leave, where both requests include 17 Jul. It is wrong for hotel rooms or equipment hire, where one guest checks out on the morning another checks in. For those, use strict comparisons:

=COUNTIFS($A$2:$A$7,A2,$B$2:$B$7,"<"&C2,$C$2:$C$7,">"&B2)-1

Be careful with the -1 here. With strict comparisons, a one-day booking (same start and end) doesn't match itself, so the self-count is lost. If your list has same-day bookings, count them separately or keep the inclusive version.

Common mistakes with overlapping dates#

These cause most wrong results:

1. Dates stored as text. A date typed as text won't compare correctly. Check with =ISNUMBER(B2), which should return TRUE.
2. Forgetting the -1. Every row overlaps itself, so without it every row shows at least 1.
3. Ranges not locked. Without $, the ranges shift as you fill down and miss rows.
4. End before start. A typo like 14 Jun instead of 14 Jul breaks every check. Add a test such as =C2>=B2.
5. Dates with times. If cells hold times as well as dates, an end of 10 Jul 09:00 is earlier than 10 Jul 17:00. Strip times with INT if you only care about days.

The IF with dates guide has more ways to compare dates safely.

Practise it#

Try the exercise on this page to find Ana's clash with COUNTIFS. Then practise dates in criteria in a live spreadsheet, where you count and total rows between two dates, and build multi-condition checks in the COUNTIFS and SUMIFS lesson. Each lesson checks your formula instantly.