Excel: Check Whether Time Ranges Overlap Across Midnight
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.
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.
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.
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.
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)`.
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.
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.
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.
Build overnight date-time values with worksheet date and time columns
Use helper columns when users enter dates and times separately or when many shifts need repeatable calculations.
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. 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. 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. 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. Copy and review the results: Drag the fill handle down to apply the formula to other rows and check which cells return TRUE.

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.




