Function Problems

Calculate Overnight Premium Hours in Excel

Kushani NimanthikaKushani Nimanthika Nov 18, 2026 868 views

Question details

Calculate the portion of an employee's shift that falls within an 18:00 to 07:00 premium period.

Calculate Overnight Premium Hours in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet contains shift start and end times, including shifts that cross midnight.
Observed behavior
The desired result is the number of hours worked between 18:00 and 07:00.
Before you start

Enter the shift start time in A2 and end time in B2 as recognizable spreadsheet times. The formulas below assume each shift is less than 24 hours; equal start and end times are treated as a zero-hour shift.

Solution 1Recommended

Calculate Premium Hours with an Overnight Overlap Formula

Use helper cells to normalize the shift times, then calculate the overlap with the premium period.

The calculation checks the early-morning and evening premium intervals on the start day, plus the early-morning interval on the following day. It returns decimal hours.

1
Normalize the start time

In C2, enter =MOD(A2,1) to extract the shift start time.

2
Adjust the end time

In D2, enter =MOD(B2,1)+IF(MOD(B2,1)<C2,1,0) to treat an earlier end time as occurring the next day.

3
Calculate the overlap

In E2, enter =24*(MAX(0,MIN(D2,7/24)-C2)+MAX(0,MIN(D2,1)-MAX(C2,18/24))+MAX(0,MIN(D2,1+7/24)-MAX(C2,1))).

4
Format and test the result

Select E2 and choose Home > Number > Number. A 12:00–20:00 shift should return 2, and a 22:00–06:00 shift should return 8. Drag the fill handle down to apply the formulas to other rows.

Calculate Premium Hours with an Overnight Overlap Formula
Shift-length limitation: The formulas use only the time portion of each entry. They do not distinguish shifts of 24 hours or longer, even if dates are included.
Calculate premium hours in WPS

Calculate Overnight Premium Hours in WPS Spreadsheets

Use WPS Spreadsheets to normalize shift times and calculate the hours that overlap the 18:00–07:00 premium period.

  1. 1. Enter the shift times: Open WPS Spreadsheets and enter the start time in A2 and the end time in B2. Use shifts shorter than 24 hours.
  2. 2. Normalize the start: Enter =MOD(A2,1) in C2.
  3. 3. Adjust the end: Enter =MOD(B2,1)+IF(MOD(B2,1)<C2,1,0) in D2.
  4. 4. Calculate premium hours: Enter =24*(MAX(0,MIN(D2,7/24)-C2)+MAX(0,MIN(D2,1)-MAX(C2,18/24))+MAX(0,MIN(D2,1+7/24)-MAX(C2,1))) in E2.
  5. 5. Format and copy the formulas: Select E2 and choose Number from the Home tab's number format options. Test 12:00–20:00 for a result of 2, then drag the fill handles down for additional shifts.
Calculate overnight premium hours with spreadsheet formulasOpen and edit Microsoft Excel XLSX filesUse familiar spreadsheet cells and formula functionsApply the calculation to multiple employee shifts
QA img-9

Frequently Asked Questions

How many premium hours are in a 22:00 to 06:00 shift?

The shift has 8 premium hours because it falls entirely within the 18:00–07:00 period. Enter 22:00 in A2 and 06:00 in B2 to test the formula.

Why does the formula add 1 to some end times?

Spreadsheet times are fractions of a day. In D2, adding 1 when the end time is earlier than the start time places the end on the following day.

Can I use the formulas if my cells include dates?

Yes, for shifts shorter than 24 hours. MOD(A2,1) and MOD(B2,1) extract the times, but the formulas do not use the dates to identify longer shifts.

Why does a shift with the same start and end time return zero?

The formulas treat equal times as a zero-hour shift, not a 24-hour shift. Use a date-aware calculation if a shift can last 24 hours or more.