Night Shift Hours in Excel: Calculating Time Across Midnight

Why 10 PM to 6 AM returns a negative number in Excel and the three formulas that fix it.

Updated 2026-10-20

A shift from 10:00 PM to 6:00 AM is eight hours, but =(C2-B2)*24 returns −16. Excel sees 6:00 AM as 0.25 and 10:00 PM as 0.9167, subtracts, and gets −0.6667 of a day. Here are the fixes, simplest first.

Fix 1: Add a day when out is before in

=(C2-B2+(C2<B2))*24

(C2<B2) evaluates to TRUE (1) when the out time is earlier, adding exactly one day. For a daytime shift it is FALSE (0) and changes nothing. This is the formula used in all of our templates.

Fix 2: MOD

=MOD(C2-B2,1)*24

MOD(x,1) keeps the fractional part and makes it positive, so −0.6667 becomes 0.3333, or 8 hours. It is shorter but hides the intent, and an in time equal to the out time returns 0 rather than 24.

Fix 3: Full dates

If you also record the date of each punch, combine date and time and subtract real timestamps:

=((C_date+C_time)-(B_date+B_time))*24

This is the only approach that handles shifts longer than 24 hours and is what payroll systems do internally.

Which day does the shift belong to?

For overtime purposes most employers assign the whole shift to the day it starts. California’s daily overtime looks at the “workday”, a fixed 24-hour period the employer defines, so a shift that starts at 10 PM and ends at 6 AM counts all 8 hours toward the day it began. Keep one row per shift, dated by the start, and the weekly formulas work unchanged.

Breaks on a night shift

The break subtraction is the same: -D2/60 for minutes, or subtract the lunch span with its own midnight guard if the lunch itself crosses midnight:

=((C2-B2+(C2<B2))-(E2-D2+(E2<D2)))*24

Worked example

In 9:45 PM, out 6:15 AM, 30-minute break. 6:15 AM is 0.2604, 9:45 PM is 0.9063, difference −0.6458, plus 1 = 0.3542, times 24 = 8.5, minus 0.5 = 8.0 hours.

The Time Card Calculator applies the same rule automatically: any out time earlier than the in time is treated as the next day, and the 24-hour shift checkbox covers the identical in and out case.

Multi-day timesheet with night shifts

A night worker’s week in a spreadsheet, with the date of the shift start in column A:

Every row uses =(C2-B2+(C2<B2))*24-D2/60. The week totals 39.5 hours with no overtime. Assigning each shift to its start date keeps the weekly grouping simple and matches how most employers define the workday.

Shifts longer than 24 hours

The one-day guard cannot distinguish a 2-hour shift from a 26-hour one, because both have the same in and out clock times. On-call doctors, firefighters and caregivers with sleep-in shifts need the full-date method: record date and time for each punch and subtract the combined date-times.

Daylight saving nights

The night the clocks go forward, a 10 PM to 6 AM shift is 7 hours of real time; the night they go back, it is 9 hours. Clock-only formulas always return 8. The FLSA requires paying for actual hours worked, so an employee who worked the extra hour must be paid for 9. Full timestamps in UTC, or a manual adjustment on those two nights, are the only ways to get this right in a spreadsheet.

Frequently asked

Which date should a night shift be paid under? The day it starts, in most policies. California defines the workday as any fixed 24-hour period and overtime is measured within it, so an employer with a workday starting at midnight splits the shift across two days; most instead set the workday to start at the shift start to keep it whole.

Why does MOD return 0 for a 24-hour shift? Because the in and out times are identical. Add a column for shift length flags or use full dates.

Does the break formula need a midnight guard? Only if the break itself can cross midnight, which it does for a lunch taken at 11:45 PM to 12:15 AM. The four-punch formula above includes the guard on the lunch span too.