Calculation Issues

How to Find Earliest and Latest Time Across Midnight in Excel

Emma BrownEmma Brown Nov 13, 2026 869 views

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.
Before you start

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.

Solution 1Recommended

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.

1
Select the first end-time cell

Click the cell where the corrected continuous end time should appear, such as C2.

2
Enter the IF correction formula

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.

3
Fill the formula down the column

Drag the fill handle down to apply the correction to every shift row in the timesheet.

4
Use MIN and MAX instead of SMALL and LARGE

In the summary sheet, enter =MIN(C2:C10) for the earliest corrected time and =MAX(C2:C10) for the latest corrected time.

5
Apply the [h]:mm custom format

Select the result cells, press Ctrl+1, click Custom, type [h]:mm, and click OK so times display continuously across midnight.

Shift now works as one period: An 8 PM to 4 AM shift is returned as a single continuous work period instead of two separate times.
WPS Spreadsheets formula fix

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. 1. Open your timesheet in WPS Spreadsheets: Launch WPS Office, click Menu > Open, and select the Excel workbook containing the shift times.
  2. 2. Insert the midnight correction formula: Click the first end-time cell, enter =IF(B2<A2,B2+1,B2), and press Enter.
  3. 3. Fill the formula down: Drag the fill handle down the column to apply the correction to all shifts.
  4. 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. 5. Apply [h]:mm formatting: Select the result cells, press Ctrl+1, click Custom, type [h]:mm, and click OK.
Supports the same Excel IF, MIN, MAX and custom time formulasFull Microsoft Excel .xlsx and .xls file compatibilityFree and lightweight with a familiar spreadsheet interfaceNo file conversion required when switching from Microsoft OfficeAvailable on Windows, macOS, Linux, Android and iOS
microsoft office alternative - wps office

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.