How to find overlapping date/time ranges in Excel?
In Excel, overlapping date or time ranges can lead to scheduling conflicts, resource allocation issues, or data integrity problems. Efficiently identifying overlaps is crucial for managing rosters, event planning, booking systems, or project timelines where one period should not coincide with another. This article provides step-by-step guidance on different practical approaches for finding overlapping date or time ranges in Excel, as demonstrated in the screenshot below.
Check overlapping date/time ranges with formula
Check overlapping date/time ranges with formula
When you need to systematically check if date or time ranges overlap, Excel formulas can offer a quick and flexible solution. This approach is suitable for small-to-medium datasets, or when you need a logical output (TRUE or FALSE) indicating overlaps on a row-by-row basis.
Typical Use Cases: Employee shift scheduling, event bookings, project phase tracking, or rental management where each row represents an interval with a start and end date or time.
Limitations: While efficient for moderate lists, formulas may be less ideal for very large datasets or for generating comprehensive overlap reports across multiple records.
1. Select all cells containing your start dates. With the range highlighted, click in the Name Box (the field left of the formula bar) and type a descriptive name, such as startdate. Press Enter to confirm. This step allows you to easily reference the full list in formulas. See screenshot:
2. Similarly, select the end date cells, enter a range name in the Name Box such as enddate, and press Enter again. Naming ranges makes your formula readable and reusable.
3. Click a blank cell in the same row as your first record—for example, C2—where you want the overlap results to appear. Enter the following formula:
=SUMPRODUCT((A2<enddate)*(B2>=startdate))>1Replace A2 with the cell containing your record’s start date and B2 with its end date. enddate and startdate use the names you defined. This formula checks if your current interval overlaps with any other in the list. Press Enter, then drag the fill handle down for all rows you want to check. For each row, TRUE means the respective range overlaps with at least one other, otherwise no overlap was found.
Make sure both startdate and enddate refer to the entire columns containing the start and end values. Adjust cell references as needed if your columns differ or your ranges have headers.
Important Notes & Troubleshooting:
- If you receive a #VALUE! error, confirm your range names and references are correct and that your date columns do not contain text or malformed date/time data.
- This approach considers overlapping cases where timespans are not fully distinct. Intervals touching only at endpoints (where the end date of one is exactly the start date of another) are generally not considered overlapping, but you can modify the inequality in the formula to adjust this behavior.
- For time ranges (including hours/minutes), the formula works the same way as with dates, provided cells are formatted consistently as times/dates.
VBA Code - Automate detection of overlapping date/time ranges for larger datasets or to generate reports
If you regularly work with large datasets and need a more automated way to identify overlaps—especially when generating summary reports or marking all conflicting entries at once—using VBA can greatly streamline the process. This approach eliminates manual checks, is suitable for hundreds or thousands of intervals, and can be customized to highlight or list all overlap pairs.
When to use: Recommended for advanced users managing large scheduling databases, shared resources, or anyone needing to generate logs of all detected overlaps, rather than a simple TRUE/FALSE row flag.
Potential drawbacks: Requires enabling macros, some familiarity with VBA, and careful data backup before running for the first time to prevent accidental overwrites.
1. Click Developer Tools > Visual Basic to open the Microsoft Visual Basic for Applications window. Then click Insert > Module and paste the code below into the module window:
Sub FindOverlappingDateRanges()
Dim ws As Worksheet
Dim i As Long, j As Long
Dim lastRow As Long
Dim overlapList As String
Dim msg As String
Dim Start1, End1, Start2, End2
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws = ActiveSheet
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row ' Assumes data starts in row 2
overlapList = ""
For i = 2 To lastRow
Start1 = ws.Cells(i, 1).Value
End1 = ws.Cells(i, 2).Value
If Start1 <> "" And End1 <> "" Then
For j = 2 To lastRow
If i <> j Then
Start2 = ws.Cells(j, 1).Value
End2 = ws.Cells(j, 2).Value
If Start2 <> "" And End2 <> "" Then
If Start1 < End2 And End1 > Start2 Then
overlapList = overlapList & "Row " & i & " overlaps with Row " & j & vbCrLf
End If
End If
End If
Next j
End If
Next i
If overlapList <> "" Then
msg = "The following rows have overlapping date/time ranges:" & vbCrLf & overlapList
Else
msg = "No overlapping date/time ranges found."
End If
MsgBox msg, vbInformation, "KutoolsforExcel"
End Sub2. After entering the code, click Run or press Enter to run the code. The macro scans pairs of date ranges in columns A (Start) and B (End), reporting any overlaps it finds. It will show a message box listing all rows that have conflicts, making it easier to audit or investigate.
- Ensure start and end dates are in columns A and B, starting from row 2 (row 1 as header). Adjust ranges if your data is organized differently.
- All cells must contain valid date/time values without blanks in the compared range.
- Back up important files before running or adapting VBA code, to avoid data loss.
Tip: You can enhance the VBA code to mark overlaps directly in the worksheet by coloring rows or writing results to an adjacent column.
Conditional Formatting - Visually highlight overlapping ranges directly in the worksheet for easier identification
Conditional Formatting is a practical way to visually flag overlapping date or time intervals directly in your spreadsheet. This solution is especially useful in busy schedules, Gantt charts, or event timelines, where you want to see at a glance which records are conflicting.
Best for: Users who want immediate on-sheet feedback or color cues without using formulas in every row or running code. Great for interactive data checks and presentations.
Limitations: Large datasets may experience slow responsiveness; and while overlaps are highlighted, detailed pairs and counts are not generated.
How to apply:
- Select the range of start dates (e.g., A2:A100) and end dates (B2:B100), or select both columns together if ranges are side-by-side.
- On the Home tab, click Conditional Formatting > New Rule.
- Choose Use a formula to determine which cells to format.
- Enter this formula into the formula box (assuming your selection starts from row 2):
=SUMPRODUCT(($A2<$B$2:$B$100)*($B2>$A$2:$A$100))>1 - Click Format…, choose a fill color to highlight overlapping ranges, and click OK to apply.
After the rule is applied, any row where the selected interval overlaps with any other in your range will be visually highlighted, making it easier to spot issues without reading each entry individually.
Tip: Adjust $A$2:$A$100 and $B$2:$B$100 to match your actual data range, and ensure references match your selection's first row.
Precautions: If you want to highlight only one of the two columns (for example, just start dates), still use the corresponding formula logic. Consider overlapping at boundaries based on your specific logic needs.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in