Cellwise Try it free

Excel guide · 4 min read

Calculate hours worked overnight in Excel

Quick answerUse =MOD(D2-C2,1)*24, where C2 is the start time and D2 the end time. MOD adds a day when the shift ends after midnight, so 10:00 PM to 6:30 AM gives 8.5 hours instead of a negative number. Subtract an unpaid break with -E2/60 when the break is in minutes.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

ABCDE
1EmployeeDateStartEndBreak (min)
2RosaNov 2, 202622:006:3030
3DevNov 2, 202614:0022:3030
4KimNov 3, 202623:157:4545
5SamNov 3, 20267:0015:3030
6LeeNov 4, 202620:004:000
7AnaNov 4, 202621:305:4530

Download this sheet as an Excel practice file (.xlsx)

Why end minus start goes wrong overnight

Excel stores a time as a fraction of a day: 6:00 AM is 0.25 and 10:00 PM is about 0.917. A shift from 22:00 to 6:30 the next morning gives 0.27 minus 0.917, which is negative.

Formatted as a time, a negative result shows as ##### in Excel's default date system. Formatted as a number it shows a meaningless negative fraction, which is what you see here.

=D2-C2

Result: -0.64583333333

-0.646: the end time looks earlier than the start, so plain subtraction fails for every shift that crosses midnight.

The fix: MOD(end - start, 1)

MOD returns the remainder after dividing by 1, and for a negative number it wraps around to a positive one. -0.646 becomes 0.354, which is 8 hours 30 minutes. For a day shift the difference is already positive and less than 1, so MOD leaves it alone. One formula covers both kinds of shift.

Multiply by 24 to turn the fraction of a day into decimal hours, which is what you need for pay. Fill the formula down the column.

=MOD(D2-C2,1)*24

Result: 8.5

8.5 hours for Rosa's 10:00 PM to 6:30 AM shift.

Subtract the unpaid break

Breaks are usually logged in minutes. Divide by 60 to get hours, then subtract. Kim worked 11:15 PM to 7:45 AM with a 45 minute break.

Round to 2 decimals so the pay calculation does not carry tiny floating-point leftovers like 7.7500000001. If your payroll rounds to the nearest quarter hour, use =MROUND(hours,0.25) in Excel instead.

=ROUND(MOD(D4-C4,1)*24-E4/60,2)

Result: 7.75

7.75 paid hours: 8.5 hours on the clock minus 0.75 hours of break.

Total a week of shifts past 24 hours

Adding times with a normal time format resets at 24 hours, so 50 hours shows as 2:15. Use the [h]:mm format, where the square brackets mean "do not roll over".

SUMPRODUCT applies MOD to every row at once, so you do not need a helper column. In a cell you would apply the format with Home > Format Cells > Custom > [h]:mm; here TEXT shows the same display.

=TEXT(SUMPRODUCT(MOD(D2:D7-C2:C7,1)),"[h]:mm")

Result: 50:15

50:15 on the clock across the six shifts (before breaks). In decimal hours that is 50.25.

Count night hours (10 PM to 6 AM)

Many employers pay a night differential for hours between 10 PM and 6 AM. The formula below clips each shift to that window. D2+(D2<C2) moves the end time to the next day when the shift crosses midnight, the first MAX part counts the hours from 10 PM onward and the second counts hours before 6 AM for shifts that start in the early morning.

22/24 is 10:00 PM and 1+6/24 is 6:00 AM the next day. Change them for your own window, for example 23/24 for 11 PM.

=(MAX(0,MIN(D6+(D6<C6),1+6/24)-MAX(C6,22/24))+MAX(0,MIN(D6+(D6<C6),6/24)-C6))*24

Result: 6

6 night hours for Lee's 8 PM to 4 AM shift: the 2 hours before 10 PM are regular hours.

Shifts longer than 24 hours: store the date too

MOD assumes a shift is shorter than a day. For a 26-hour on-call stretch, or when you need the exact clock-out day, store full date-times: date plus time in one cell, or add the date column to each time.

Then the subtraction is simply end date-time minus start date-time, and it is never negative. Here the end is on the next day whenever the end time is earlier than the start.

=ROUND((B2+D2+(D2<C2)-(B2+C2))*24,2)

Result: 8.5

8.5 hours again, worked out from real date-times: Nov 2 at 22:00 to Nov 3 at 6:30.

Common mistakes

Getting ##### or a negative number

The shift crosses midnight. Wrap the subtraction in MOD(end-start,1), or store full dates with the times.

A weekly total of 50 hours shows as 2:00

Use the custom format [h]:mm. Without brackets Excel shows only the hours left after whole days.

Paying 0.354 instead of 8.5

Times are fractions of a day. Multiply by 24 before multiplying by the hourly rate.

Times typed as text, such as "10pm"

Excel needs a space: 10 PM or 22:00. If a time sits on the left of the cell, it is text; retype it or use Data > Text to Columns to convert the column.

FAQ

How do I calculate hours worked in Excel when the shift goes past midnight?

Use =MOD(end-start,1)*24. MOD turns the negative overnight difference into the correct positive time, and *24 converts it to hours.

Why does Excel show ##### when I subtract times?

The result is negative because the end time is after midnight. Excel cannot display negative times in its default date system. Use =MOD(end-start,1) instead.

How do I add up hours over 24 in Excel?

Sum the times and format the cell as [h]:mm (Format Cells > Custom). The brackets stop the total resetting every 24 hours.

How do I subtract a 30 minute lunch break?

With the break in minutes in E2: =MOD(D2-C2,1)*24-E2/60. If the break is a time like 0:30, subtract it inside: =(MOD(D2-C2,1)-E2)*24.

Functions used

Related guides

Updated