How to Calculate Night Shift Hours in Excel

Overnight shifts are where simple timesheet formulas break. A shift that starts at 10:00 PM and ends at 6:30 AM looks to Excel as if it ended before it began. This guide explains the fixes, from a one-line MOD formula to date-stamped punches, then counts night differential hours and covers two payroll details Excel won’t handle for you: workweek boundaries and daylight saving time. For the basics of a daily hours sheet, see calculate hours worked in Excel.

Why overnight shifts show

Excel stores a time as a fraction of a day: 10:00 PM is 22/24 and 6:30 AM is 6.5/24. Subtracting start from end gives:

Excel’s default 1900 date system can’t display a negative time, so the cell fills with #####. The answer you want is 8.5 hours: 2 hours before midnight plus 6.5 after. Every fix below does the same thing: it adds one day (the number 1) when the end time is earlier than the start time.

Fix 1: MOD

With Time In in B2 and Time Out in C2:

=MOD(C2-B2,1)
=MOD(C2-B2,1)*24

MOD(x,1) returns the remainder after dividing by 1, and Excel’s MOD always takes the sign of the divisor, so −15.5/24 becomes 1 − 15.5/24 = 8.5/24. The first formula shows 8:30 when formatted h:mm; the second returns 8.50 decimal hours (format it as Number, 0.00). Same-day shifts are unaffected, because a positive difference is already between 0 and 1.

Fix 2: the (C2<B2) trick

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

The comparison C2<B2 returns TRUE when the shift crosses midnight, and Excel treats TRUE as 1 in arithmetic, so it adds exactly one day. The IF version is longer but easier for coworkers to read:

=IF(C2<B2,C2+1-B2,C2-B2)*24

All three formulas give identical results, and they share one limit: they assume a shift shorter than 24 hours. A 7:00 AM to 7:00 AM shift returns 0.

Shifts of 24 hours or longer: add dates

Firefighters, medical residents, on-call staff and drivers sometimes work 24 hours or more, and no time-only formula can tell a 2-hour shift from a 26-hour one. Record a date with each punch: B = Start Date, C = Start Time, D = End Date, E = End Time. Then:

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

Adding a date (a whole number) to a time (a fraction) creates a full timestamp, so no MOD is needed:

If your time clock exports one combined date-and-time value per punch, plain subtraction such as =(C2-B2)*24 works. To show hours and minutes instead, leave off the *24 and format the cell with Ctrl+1 (Cmd+1) → Custom → [h]:mm; with plain h:mm, 36 hours displays as 12:00. In Google Sheets, use Format → Number → Duration. For a one-off check, the time duration calculator does the same math.

Count night differential hours (10 PM to 6 AM)

Many employers and union contracts pay a premium for night hours. To count the hours that fall between 22:00 and 06:00, compare the shift with two windows: 10:00 PM to 6:00 AM the next morning, and midnight to 6:00 AM on the start day (for shifts that begin after midnight). With Time In in B2 and Time Out in C2, put total hours in D2 and night hours in E2:

=MOD(C2-B2,1)*24
=(MAX(0,MIN(C2+(C2<B2),30/24)-MAX(B2,22/24))+MAX(0,MIN(C2+(C2<B2),6/24)-MAX(B2,0)))*24

C2+(C2<B2) is the end time, pushed into the next day when needed. 22/24 is 10:00 PM and 30/24 is 6:00 AM the next day. Each MAX(0, MIN(…) − MAX(…)) term measures how much of the shift overlaps one window, and the two overlaps are added. Day hours in F2 are simply =D2-E2.

For a different window, such as 11:00 PM to 7:00 AM, replace 22/24, 30/24 and 6/24 with 23/24, 31/24 and 7/24. The formula works for any night window that crosses midnight and any shift shorter than 24 hours.

Paying the differential

A flat per-hour premium is the simplest to calculate. With the base rate in I1 and the night premium in I2:

=D2*$I$1+E2*$I$2

A 7:00 PM to 7:00 AM shift at $22.00 an hour with a $2.00 night premium pays 12 × $22.00 + 8 × $2.00 = $280.00. For a percentage differential, such as 10% of the base rate, use =D2*$I$1+E2*$I$1*10%.

The FLSA does not require a night-shift premium; it comes from employer policy or a union contract. When one is paid, it generally must be included in the regular rate used for overtime (29 CFR Part 778), so overtime in a week with night premiums is worth more than 1.5 × the base rate. This is general information, not legal advice; state law or your contract may be stricter.

Which day and workweek do overnight hours belong to?

Under the FLSA, overtime is figured per workweek: a fixed, regularly recurring period of 168 hours (seven consecutive 24-hour periods) that the employer chooses, such as Sunday 12:00 AM through Saturday 11:59 PM. Hours generally count in the workweek in which they are actually worked, so a Saturday 10:00 PM to Sunday 6:30 AM shift puts 2 hours in one week and 6.5 in the next. Split a shift at midnight with:

=IF(C2<B2,1-B2,C2-B2)*24
=IF(C2<B2,C2,0)*24

For 10:00 PM to 6:30 AM, these return 2.00 hours before midnight and 6.50 after. Many employers show the whole shift on its start date for scheduling, which is fine for display, but make sure weekly overtime uses the actual split, or set the workweek to begin at an hour when no one is on shift. State daily-overtime rules use their own workday definitions (California, for example, uses a consecutive 24-hour period that begins at the same time each day). See FLSA overtime rules explained for the federal rules and calculate overtime in Excel for weekly formulas.

Weekly totals when a night premium is paid

Because the differential belongs in the regular rate, a week with overtime needs one extra step. Suppose five shifts of 10:00 PM to 6:30 AM sit in rows 2–6, with total hours in D and night hours in E, a $20.00 base rate in I1 and a $2.00 night premium in I2. Add these cells:

D7: =SUM(D2:D6)
E7: =SUM(E2:E6)
H7: =D7*$I$1+E7*$I$2
H8: =H7/D7
H9: =0.5*H8*MAX(0,D7-40)
H10: =ROUND(H7+H9,2)

D7 is 42.50 hours and E7 is 40.00 night hours. Straight-time pay in H7 is 42.5 × $20 + 40 × $2 = $930.00, the regular rate in H8 is $930 ÷ 42.5 ≈ $21.88, the overtime premium in H9 is 0.5 × $21.88 × 2.5 ≈ $27.35, and gross pay in H10 is $957.35. Paying overtime on the $20 base alone would come to $955.00 and underpay by $2.35.

Daylight saving time

Excel knows nothing about time zones or clock changes; it just subtracts clock readings. In most of the U.S., clocks spring forward at 2:00 AM on the second Sunday in March and fall back at 2:00 AM on the first Sunday in November. On those nights a 10:00 PM to 6:30 AM shift lasts:

Pay is owed for the time actually worked, so add an adjustment column G that holds −1 or 1 on those nights (blank otherwise), and calculate total hours with:

=MOD(C2-B2,1)*24+G2

Because the change happens at 2:00 AM, the extra or missing hour falls inside a 10 PM to 6 AM window, so apply the same adjustment to night hours for any shift that spans 2:00 AM.

Common errors and fixes

• ##### on overnight rows. The result is a negative time. Use MOD or the (C2<B2) version.
• A 24-hour shift shows 0. Time-only formulas can’t see whole days. Add date columns.
• Night hours of 0 for an overnight shift. The end time inside the night formula must be C2+(C2<B2); using plain C2 makes overnight shifts miss both windows.
• 12:00 PM instead of 12:00 AM. A 4:00 PM to midnight shift entered with an end time of 12:00 PM calculates as 20 hours. Midnight is 12:00 AM, or 0:00 in 24-hour form.
• Military times typed as numbers. An entry like 2230 is the number 2,230, not a time. Convert it with =TIME(INT(B2/100),MOD(B2,100),0), or check values with the military time converter.
• A 36-hour total displays as 12:00. Use the [h]:mm format, or work in decimal hours.