How to Find Earliest and Latest Time Across Midnight in Excel
Question details
The user needs to calculate the earliest and latest shift times in an Excel timesheet when shifts cross midnight, because SMALL and LARGE return incorrect results.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user has a timesheet with shifts that start in the evening and end after midnight, and wants a summary sheet to show the first and last shift times as one continuous work period.
- Observed behavior
- SMALL and LARGE formulas treat times after midnight as earlier than the previous evening, so the earliest and latest shift times are incorrect.
Confirm that the timesheet cells contain real Excel time values rather than text, and locate the shift start time cell that every other time will be compared against.
Correct after-midnight times with IF and then use MIN/MAX
Use this method when SMALL or LARGE returns the wrong earliest or latest time because the shift crosses midnight.
Excel stores times as fractions of a 24-hour day, so 4 AM appears smaller than 8 PM. Adding 1 day to times that are earlier than the shift start converts them into a continuous timeline that MIN and MAX can rank correctly.
Click the cell where the corrected continuous end time should appear, such as C2.
Type =IF(B2<A2,B2+1,B2) and press Enter. This adds 1 day to any end time in B2 that is earlier than the start time in A2.
Drag the fill handle down to apply the correction to every shift row in the timesheet.
In the summary sheet, enter =MIN(C2:C10) for the earliest corrected time and =MAX(C2:C10) for the latest corrected time.
Select the result cells, press Ctrl+1, click Custom, type [h]:mm, and click OK so times display continuously across midnight.
Use a compact TRUE/FALSE addition formula for times after midnight
Use this shorter alternative when you want a single formula without nesting IF.
Find Earliest and Latest Shift Times Across Midnight in WPS Spreadsheets
Open your Excel timesheet in WPS Spreadsheets and use the same IF, MIN and MAX formulas with [h]:mm formatting to treat after-midnight times as one continuous period. WPS Office keeps .xlsx files and formulas intact.
- 1. Open your timesheet in WPS Spreadsheets: Launch WPS Office, click Menu > Open, and select the Excel workbook containing the shift times.
- 2. Insert the midnight correction formula: Click the first end-time cell, enter =IF(B2<A2,B2+1,B2), and press Enter.
- 3. Fill the formula down: Drag the fill handle down the column to apply the correction to all shifts.
- 4. Use MIN and MAX on the corrected column: In your summary cell, enter =MIN(C2:C10) for the earliest time and =MAX(C2:C10) for the latest.
- 5. Apply [h]:mm formatting: Select the result cells, press Ctrl+1, click Custom, type [h]:mm, and click OK.

Frequently Asked Questions
Why does Excel think 4 AM is earlier than 8 PM?
Excel stores times as fractions of a 24-hour day, so 4 AM is about 0.1667 and 8 PM is about 0.8333. For shifts crossing midnight, add 1 day to times that are earlier than the shift start so they continue past the previous evening.
Can I still use SMALL and LARGE after correcting the times?
Yes. Once you convert all after-midnight times to continuous values using =IF(B2<A2,B2+1,B2) or =B2+(B2<A2), SMALL and LARGE will rank them correctly just like MIN and MAX.
What number format stops the result from showing as a date?
Use the custom number format [h]:mm. Select the cells, press Ctrl+1, click Custom, enter [h]:mm, and click OK. This displays the continuous time without rolling over at midnight.
Does this formula work in WPS Spreadsheets?
Yes. WPS Spreadsheets supports the same IF, MIN, MAX and custom time formulas, and it opens Excel .xlsx timesheets natively.
How do I handle shifts that span more than 24 hours?
The IF method handles a single midnight crossing. For shifts longer than 24 hours, store full date and time values in the cells or add elapsed days based on the shift duration.




