Calculation Issues

Excel: Check Whether Time Ranges Overlap Across Midnight

Amos GikundaAmos Gikunda Nov 12, 2026 868 views

Question details

The user needs to compare appointment and work-shift time ranges when one or both ranges continue into the next calendar day.

Product
Microsoft Excel
Device & OS
not provided
Scenario
An overnight shift, such as 10:30 PM to 6:00 AM, must be compared with an appointment or another time range.
Observed behavior
Time-only values can make an overnight range appear reversed because the ending time is earlier than the starting time; the goal is to identify overlap accurately.
Before you start

Decide whether ranges that only touch at an endpoint count as overlapping. Prepare separate cells for each range's full date-and-time start and end values, and format them as date and time.

Solution 1Recommended

Combine dates and times, then test the ranges with an overlap formula

Use this method for reliable overlap checks when a shift or appointment crosses midnight.

Excel stores a date and time as one serial value, so an overnight range must use the next calendar date for its ending value. For example, a shift from 10:30 PM on November 13 to 6:00 AM on November 14 should be stored as 13-Nov-2023 22:30 through 14-Nov-2023 06:00.

The formula below treats ranges that only touch at an endpoint as non-overlapping. Use less-than-or-equal comparisons if touching ranges should count as overlapping.

1
Create full shift date-time values

In a shift start cell, enter `=DATE(2023,11,13)+TIME(22,30,0)`. In the shift end cell, enter `=DATE(2023,11,14)+TIME(6,0,0)`, using the next date for the overnight ending time.

2
Create full appointment date-time values

Enter the appointment's actual start and end dates and times in two separate cells, such as `=DATE(2023,11,14)+TIME(4,0,0)` and `=DATE(2023,11,14)+TIME(5,30,0)`.

3
Format the cells for readability

Select the date-time cells, open the Home tab, choose the Number Format menu, and apply a date-and-time format so the calendar date is visible.

4
Check whether the ranges overlap

If the shift start is in A2, shift end in B2, appointment start in C2, and appointment end in D2, enter `=AND(C2<B2,D2>A2)` in a result cell. TRUE means the ranges overlap.

5
Check whether the appointment is fully inside the shift

To confirm that the appointment starts and ends within the shift, enter `=AND(C2>=A2,D2<=B2)`. TRUE means the entire appointment is contained by the shift.

Overnight ranges need two calendar dates: Never compare only 22:30 and 06:00 as time values for an overnight shift. Assign 06:00 to the following date so Excel can compare the sequence correctly.
WPS Spreadsheet solution

Check Overnight Time Range Overlaps in WPS Spreadsheet

WPS Spreadsheet can combine calendar dates with time values and evaluate overnight appointments or shifts using the same AND formulas. Its familiar spreadsheet interface makes it easy to format date-time cells, copy formulas, and review TRUE or FALSE results.

  1. 1. Enter complete date-time values: Open WPS Spreadsheet, enter each range's start and end as full date-time values, and assign the overnight end to the following calendar date.
  2. 2. Format the schedule cells: Select the date-time cells, open the Home tab, and choose a date-and-time format from the Number Format menu.
  3. 3. Add the overlap formula: Use `=AND(C2<B2,D2>A2)` when A2:B2 contains the first range and C2:D2 contains the second range, then press Enter.
  4. 4. Copy and review the results: Drag the fill handle down to apply the formula to other rows and check which cells return TRUE.
Accurately compares appointment and work-shift ranges that cross midnightSupports familiar Excel formulas such as DATE, TIME, and ANDCompatible with Microsoft Excel XLS and XLSX spreadsheetsLightweight spreadsheet editing for schedules, staffing, and appointments
microsoft office alternative - wps office

Frequently Asked Questions

Why does an overnight shift from 10:30 PM to 6:00 AM appear invalid in Excel?

When only times are stored, 6:00 AM has a smaller value than 10:30 PM. Store the start and end with dates, using the next calendar date for 6:00 AM, so Excel recognizes the correct sequence.

What Excel formula checks whether two time ranges overlap?

With the first range in A2:B2 and the second in C2:D2, use `=AND(C2<B2,D2>A2)`. It returns TRUE when the ranges overlap and excludes ranges that only touch at an endpoint.

How can I check whether an appointment is completely within an overnight shift?

Use `=AND(C2>=A2,D2<=B2)`, where A2 and B2 are the full shift start and end date-times and C2 and D2 are the appointment start and end date-times.

Should the ending date be the next day for every overnight range?

Yes. If a range starts on November 13 and ends after midnight at 6:00 AM, enter November 14 as the ending date. A same-day range should keep the same start and end date.