Python Excel AutomationPractical guides for developers
How-to
Advanced Data Transformation And CleaningDeep dive

Find Overlapping Date Ranges with pandas

Catch double bookings, overlapping contracts and clashing leave requests in a sheet of start and end dates: the overlap test, checking each record against the previous one per group, listing every overlapping pair, merging overlaps into continuous periods, finding gaps, and highlighting the clashes in Excel.
Dates & timespandas

Any sheet with a start date and an end date per row can contain clashes: a meeting room booked twice for the same afternoon, an employee with two overlapping leave requests, a customer with two active contracts for the same product, a vehicle assigned to two jobs at once. Spotting them by eye works for ten rows and fails for ten thousand. The test for two ranges overlapping is a single comparison, and pandas can apply it per group — per room, per employee, per contract — either against the previous row or against every other row. The same ideas then merge overlapping ranges into continuous periods and find the gaps between them. This page is part of Working with Dates and Times in Excel Data.

Prerequisites

Bash
pip install pandas openpyxl xlsxwriter

leave_requests.xlsx lists eleven leave requests for four employees with an ID, start and end date, and status. End dates are inclusive — a request from 6 to 10 July covers five days, the 10th included — which is how people write leave, bookings and contract periods. Hidden in the data: overlapping requests for two employees, one request that lies completely inside another, two requests that touch but do not overlap, and one request whose end is before its start.

Python
import pandas as pd

leave = pd.read_excel("leave_requests.xlsx", parse_dates=["Start", "End"])
print(leave.to_string(index=False))
bad = leave[leave["End"] < leave["Start"]]
print("end before start:", bad["RequestID"].tolist())
leave = leave.drop(bad.index)

Check for reversed ranges first. A range that ends before it starts overlaps nothing and makes every later calculation misleading; report it and drop it from the comparison.

Step 1 — The overlap test

Two ranges A and B overlap when each starts before the other ends. With inclusive end dates that is:

Python
def overlaps(a_start, a_end, b_start, b_end):
    return (a_start <= b_end) & (b_start <= a_end)

d = pd.Timestamp
print(overlaps(d("2026-07-06"), d("2026-07-10"), d("2026-07-09"), d("2026-07-15")))  # partial overlap
print(overlaps(d("2026-07-01"), d("2026-07-31"), d("2026-07-13"), d("2026-07-17")))  # one inside the other
print(overlaps(d("2026-07-06"), d("2026-07-08"), d("2026-07-09"), d("2026-07-10")))  # back to back

This two-comparison test covers every case — partial overlap on either side, one range inside the other, identical ranges — and nothing else. Back-to-back ranges (the 8th and the 9th) do not overlap with inclusive ends. If your data uses exclusive ends, as hotel check-out dates or time slots often do, use < instead of <=, otherwise a booking ending at 14:00 clashes with one starting at 14:00.

The cases the test has to get right Four pairs of ranges along a timeline. A partial overlap and a range inside another both count as overlapping; two ranges that touch at consecutive days and two separate ranges do not. Partial overlap 6–10 Jul and 9–15 Jul One inside another 1–31 Jul and 13–17 Jul Back to back 6–8 Jul then 9–10 Jul Separate 6–10 Jul and 3–7 Aug

Step 2 — Compare each range with the ones before it

For a quick flag per row, sort by group and start date and compare each row with the latest end date seen so far in its group. Using the running maximum of the end — not just the previous row's end — matters when one long range contains several shorter ones:

Python
leave = leave.sort_values(["Employee", "Start", "End"]).reset_index(drop=True)
prev_max_end = leave.groupby("Employee")["End"].transform(lambda s: s.cummax().shift())
leave["Overlaps previous"] = leave["Start"] <= prev_max_end
print(leave[["RequestID", "Employee", "Start", "End", "Overlaps previous"]].to_string(index=False))

Jonas's July requests show why the running maximum is needed: his month-long approved leave from 1 to 31 July contains both later requests. Comparing each row only with the row directly before it would flag the 13–17 July request (it starts before the 31st), but the 20–24 July request would be compared with the 17th and pass. cummax().shift() carries the 31st forward, so both are flagged. Adaeze's single-day request on 7 August is flagged too — it falls on the last day of her approved week — while Claire's back-to-back requests are not.

Step 3 — List every overlapping pair

A flag says that a row clashes; for resolving it, people need to know with what. Merge each group with itself, keep pairs that overlap, and drop each pair's mirror image:

Python
pairs = leave.merge(leave, on="Employee", suffixes=("", "_other"))
pairs = pairs[(pairs["RequestID"] < pairs["RequestID_other"])
              & overlaps(pairs["Start"], pairs["End"], pairs["Start_other"], pairs["End_other"])]
pairs["Days"] = ((pairs[["End", "End_other"]].min(axis=1) - pairs[["Start", "Start_other"]].max(axis=1)).dt.days + 1)
print(pairs[["Employee", "RequestID", "Status", "RequestID_other", "Status_other", "Days"]].to_string(index=False))

The condition RequestID < RequestID_other keeps each pair once and removes each request's comparison with itself. The overlap length is the later start to the earlier end, plus one day because both ends are inclusive. A self-merge compares every pair in a group, so its size grows with the square of the group size — fine for dozens of ranges per employee or room, too big for a group of a hundred thousand rows, where the sorted approach of Step 2 is the one to use.

The overlapping pairs A table of overlapping request pairs. Adaeze's L-101 and L-102 overlap by two days, L-103 and L-104 by one day; Jonas's L-105 overlaps L-106 and L-107 by five days each. A B C D E F 1 Employee Request Status Clashes with Status Days 2 Adaeze L-101 Approved L-102 Pending 2 3 Adaeze L-103 Approved L-104 Pending 1 4 Jonas L-105 Approved L-106 Pending 5 5 Jonas L-105 Approved L-107 Pending 5

Step 4 — Merge overlaps into continuous periods

The opposite question comes up just as often: how many days was each employee away, counting overlapping requests once? Merge overlapping (and, optionally, touching) ranges into continuous periods. A new period starts wherever a range begins after everything before it has ended:

Python
def merge_ranges(g, touch=True):
    g = g.sort_values("Start")
    gap = pd.Timedelta(days=1 if touch else 0)
    new_period = g["Start"] > g["End"].cummax().shift() + gap
    period = new_period.cumsum()
    return g.groupby(period).agg(Start=("Start", "min"), End=("End", "max"),
                                 Requests=("RequestID", ", ".join))

periods = leave.groupby("Employee").apply(merge_ranges, include_groups=False).reset_index(level=1, drop=True)
periods["Days"] = (periods["End"] - periods["Start"]).dt.days + 1
print(periods.to_string())
Three overlapping requests become one period On the left Jonas's three requests, one covering all of July and two shorter ones inside it. On the right a single merged period from 1 to 31 July lasting 31 days. A B C 1 Request Start End 2 L-105 01 Jul 31 Jul 3 L-106 13 Jul 17 Jul 4 L-107 20 Jul 24 Jul A B C 1 Start End Days 2 01 Jul 31 Jul 31 before after merge_ranges

new_period is True on each row that starts after the running maximum end of the rows before it; its cumulative sum numbers the periods 0, 1, 2, …, and a group-by on that number collapses each period to its first start and last end. With touch=True, ranges on consecutive days — Claire's 6–8 and 9–10 July — join into one period, which is what "continuous absence" usually means; set it to False to keep them apart. The same technique turns a list of equipment rentals into utilisation periods or contract lines into continuous customer tenure.

Step 5 — Gaps between ranges

With merged periods, the gaps are the days between one period's end and the next one's start:

Python
periods = periods.reset_index()
periods["Next start"] = periods.groupby("Employee")["Start"].shift(-1)
periods["Gap days"] = (periods["Next start"] - periods["End"]).dt.days - 1
gaps = periods.dropna(subset=["Next start"]).astype({"Gap days": int})
print(gaps[["Employee", "End", "Next start", "Gap days"]].to_string(index=False))

Gaps answer questions such as "which contracts lapsed before renewal" or "how long was the room free". Subtracting one converts the difference between two inclusive dates into the number of whole days in between.

Step 6 — Highlight the clashes in Excel

Write the requests back with the clashing rows highlighted and the pair list on its own sheet, so whoever approves leave can see the conflicts in context:

Python
clash_ids = set(pairs["RequestID"]) | set(pairs["RequestID_other"])
leave["Clash"] = leave["RequestID"].isin(clash_ids)

with pd.ExcelWriter("leave_checked.xlsx", engine="xlsxwriter", datetime_format="dd mmm yyyy") as xw:
    leave.drop(columns="Overlaps previous").to_excel(xw, sheet_name="Requests", index=False)
    pairs[["Employee", "RequestID", "RequestID_other", "Days"]].to_excel(xw, sheet_name="Clashes", index=False)
    wb, ws = xw.book, xw.sheets["Requests"]
    red = wb.add_format({"bg_color": "#FCE9E9", "font_color": "#9C0006"})
    ws.conditional_format(1, 0, len(leave), 5, {"type": "formula", "criteria": "=$F2=TRUE", "format": red})
    ws.set_column("A:E", 13)
print(sorted(clash_ids))

The formula rule colours a whole row when its Clash column is TRUE, as in Highlight an Entire Row Based on a Cell Value with openpyxl.

Common pitfalls

  • Inclusive versus exclusive ends. Use <= for inclusive end dates and < for exclusive ones; mixing them flags back-to-back ranges or misses one-day overlaps.
  • Comparing only with the previous row. A long range can contain several later ones; compare with the running maximum end.
  • Unsorted data. Every sequential method assumes rows sorted by group and start date.
  • Reversed ranges. Rows with the end before the start silently break the logic; report them first.
  • Times on the dates. A start of 9 July 00:00 and an end of 9 July 17:00 behave differently from whole days; normalise with dt.normalize() if only dates matter.

Conclusion

Two ranges overlap when each starts on or before the other's end. Sort by group and start, compare each start with the running maximum of earlier ends to flag clashes, self-merge within groups to list each overlapping pair with its length, and number continuous periods with a cumulative sum to merge overlaps and measure gaps. Check for reversed ranges first, decide whether ends are inclusive, and write the clashes back to Excel with the affected rows highlighted.

Frequently asked questions

How do I check if two date ranges overlap in pandas? Two ranges overlap when start_a <= end_b and start_b <= end_a, for inclusive end dates. Apply the comparison to whole columns — for example after a self-merge within each group — to test many pairs at once.

How do I find overlapping bookings for the same room or employee? Sort by the group column and start date, then compare each start with the running maximum of earlier end dates in the group, computed with transform(lambda s: s.cummax().shift()) on the grouped end column. Rows whose start is on or before that value overlap an earlier booking.

How do I merge overlapping date ranges into one? Sort by start, mark rows whose start is after the running maximum end of the rows before, take the cumulative sum of that mark as a period number, and aggregate each period to its minimum start and maximum end.

Do ranges that touch count as overlapping? With inclusive end dates, a range ending on the 8th and one starting on the 9th do not overlap. When merging into continuous periods you may still want to join them; allow a one-day gap in the comparison to do so.

More on dates & times

More on dates & times →