Formula Errors

Check Whether a Time Falls Between Two Times Across Midnight in Excel

Steve KSteve K Nov 18, 2026 868 views

Question details

Determine whether a time falls between a shift's start and end times, including shifts that cross midnight.

Check Whether a Time Falls Between Two Times Across Midnight in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Enter the shift times

In the worksheet, enter the start time in L2 and the end time in M2, such as 5:00 PM and 2:00 AM.

2
Enter the time to check

Type the time you want to test in Q1, such as 11:00 PM.

3
Enter the formula

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.

Use an IF formula for daytime and overnight shifts
Shift boundaries are excluded: The formula returns No when the tested time equals the start or end time. It also returns No for every time when the start and end are equal.
WPS Spreadsheets

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. 1. Open a spreadsheet: Open WPS Spreadsheets and create a workbook or open an existing Excel file.
  2. 2. Enter the shift: Enter the shift start time in L2 and the end time in M2.
  3. 3. Enter the test time: Enter the time to check in Q1.
  4. 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.
Check shifts that cross midnight with one formulaOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interfaceWork with a free, lightweight office suite
QA img-9

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.