Formula Errors

How to Check Whether a Time Range Crosses Midnight in Excel

Huma Ashraf ChHuma Ashraf Ch Nov 13, 2026 869 views

Question details

The user needs to determine whether an hourly time falls within a work shift, including shifts that start in the evening and end the next morning.

How to Check Whether a Time Range Crosses Midnight in Excel
Product
WPS Spreadsheets
Device & OS
not provided
Scenario
The user has a list of hourly times and a shift start and end time, and wants to mark which hours are inside the shift timeframe.
Observed behavior
The formula should return a marker such as "O" when the hourly time is within the shift, even when the shift end time is earlier than the shift start time.
Before you start

Confirm that the shift start, shift end, and hourly time cells are entered as real time values or time-formatted numbers. If any cell is stored as text, the formula may not calculate correctly.

Solution 1Recommended

Use MOD, MEDIAN, and an Adjusted Time Test for Midnight-Crossing Shifts

Use this formula when you need one robust check that works for both same-day shifts and overnight shifts.

The formula calculates the shift duration with MOD, adds it to the start time to create an end-of-shift marker, and then uses MEDIAN to test the hourly time. When the hourly time is earlier than the start time because it falls after midnight, the formula adds 1 to the time so the comparison treats it as the following day.

1
Enter the shift start and end times

In an empty worksheet, enter the shift start time in cell D2 and the shift end time in cell D3 as valid time values.

2
Enter the hourly time to test

In cell C4, enter the hourly time you want to check, such as 11:00 PM.

3
Select the result cell

Click the cell where you want to show the shift marker, for example E4.

4
Enter the MEDIAN and MOD formula

Type or paste =IF(MEDIAN($D$2,$D$2+MOD($D$3-$D$2,1),IF($C4<$D$2,$C4+1,$C4))=IF($C4<$D$2,$C4+1,$C4),"O","") and press Enter.

5
Copy the formula down for more times

Drag the fill handle from the result cell down over the adjacent hourly time rows to apply the check to the full list.

Use MOD, MEDIAN, and an Adjusted Time Test for Midnight-Crossing Shifts
Works for overnight shifts: The formula returns "O" when the time is within the shift and returns an empty string when it is outside, including shifts that cross midnight.
WPS Spreadsheets formula

Check Midnight-Crossing Shifts in WPS Spreadsheets

WPS Spreadsheets supports the same MOD and MEDIAN functions as Excel, so you can build the overnight shift check directly in a free, familiar spreadsheet environment without changing your formula logic.

  1. 1. Open WPS Spreadsheets: Launch WPS Office and open a blank workbook or an existing Excel workbook with shift data.
  2. 2. Enter shift and hourly time values: Type the shift start in D2, shift end in D3, and the first hourly time in C4 using time values.
  3. 3. Insert the midnight shift formula: In the result cell, enter =IF(MEDIAN($D$2,$D$2+MOD($D$3-$D$2,1),IF($C4<$D$2,$C4+1,$C4))=IF($C4<$D$2,$C4+1,$C4),"O","") and press Enter.
  4. 4. Copy the formula down: Drag the fill handle from the result cell down to check all hourly times.
Uses the same Excel-compatible MOD and MEDIAN formula for midnight-crossing shiftsPreserves .xlsx, .xls, and .csv formatting when you open and edit workbooksFree and lightweight spreadsheet tool with an Excel-like interfaceLets you copy, fill, and audit formulas across large time-tracking sheets
microsoft office alternative - wps office

Frequently Asked Questions

Why does end time minus start time fail for overnight shifts?

When a shift crosses midnight, the end time is numerically smaller than the start time, so a direct subtraction can produce a negative value. The MOD function wraps that negative result into a positive duration between 0 and 1, representing the correct hours to add to the shift start.

Can I use this formula if my times are stored as text?

No. Text values are not recognized as time serial numbers, so MOD and MEDIAN cannot calculate them correctly. Convert the cells to real time values by applying a time format or using a time function before using the formula.

How do I copy the formula to multiple hourly time rows?

Select the cell containing the formula, then drag the fill handle at the lower-right corner of the cell down across the range of hourly times. Excel or WPS Spreadsheets will adjust the C4 reference automatically for each row.

What does MOD($D$3-$D$2,1) do in this formula?

It calculates the shift duration as a positive time fraction. MOD(...,1) keeps the result between 0 and 1, so an overnight shift ending after midnight is converted into the correct portion of a 24-hour day.