Calculate Overnight Premium Hours in Excel
Question details
Calculate the portion of an employee's shift that falls within an 18:00 to 07:00 premium period.

- 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.
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.
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.
In C2, enter =MOD(A2,1) to extract the shift start 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.
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))).
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 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. 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. Normalize the start: Enter =MOD(A2,1) in C2.
- 3. Adjust the end: Enter =MOD(B2,1)+IF(MOD(B2,1)<C2,1,0) in D2.
- 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. 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.

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.




