Check Whether a Time Falls Between Two Times Across Midnight in Excel
Question details
Determine whether a time falls between a shift's start and end times, including shifts that cross midnight.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A shift may start at 5:00 PM and end at 2:00 AM, and the user wants to test a time against that range.
- Observed behavior
- The formula should return Yes for times within the shift and No for times outside it.
Enter the shift start time in L2, the end time in M2, and the time to check in Q1. Make sure the cells contain recognized time values rather than text or date-and-time values.
Use an IF formula for daytime and overnight shifts
Use this formula when the shift may end either on the same day or after midnight.
The formula uses AND when the start time is earlier than or equal to the end time. For an overnight range, it uses OR to match times after the start or before the end.
In the worksheet, enter the start time in L2 and the end time in M2, such as 5:00 PM and 2:00 AM.
Type the time you want to test in Q1, such as 11:00 PM.
Select an empty cell and enter =IF(IF(L2<=M2,AND(Q1>L2,Q1<M2),OR(Q1>L2,Q1<M2)),"Yes","No"). Press Enter to see the result.

Check overnight shift times in WPS Spreadsheets
Use the same IF, AND, and OR formula in WPS Spreadsheets to test times against daytime or overnight shifts.
- 1. Open a spreadsheet: Open WPS Spreadsheets and create a workbook or open an existing Excel file.
- 2. Enter the shift: Enter the shift start time in L2 and the end time in M2.
- 3. Enter the test time: Enter the time to check in Q1.
- 4. Calculate the result: Select an empty cell, enter =IF(IF(L2<=M2,AND(Q1>L2,Q1<M2),OR(Q1>L2,Q1<M2)),"Yes","No"), and press Enter.

Frequently Asked Questions
Why does an overnight shift need OR instead of AND?
For a shift from 5:00 PM to 2:00 AM, a matching time is either after 5:00 PM or before 2:00 AM. It cannot satisfy both comparisons at once.
How do I include the exact start and end times?
Replace the strict comparisons with inclusive ones: =IF(IF(L2<=M2,AND(Q1>=L2,Q1<=M2),OR(Q1>=L2,Q1<=M2)),"Yes","No"). When start and end are equal, this version matches only that exact time.
What result should I get for 1:00 AM on a 5:00 PM to 2:00 AM shift?
Enter 5:00 PM in L2, 2:00 AM in M2, and 1:00 AM in Q1. The formula returns Yes because 1:00 AM is before the end time.
Why might the formula give an unexpected result for a date and time?
The formula compares cell values, so a date attached to a time changes the comparison. Use time-only values in L2, M2, and Q1, and check that they are recognized as times rather than text.




