Excel 計算跨夜工作時長：免費工時表

Tested in: Live formula checks in Microsoft Excel 16.114.4 for Mac (English UI; system region zh-Hans_US), November 8, 2026; Google Sheets (spreadsheet locale zh_CN), November 9, 2026. Results and platform differences are noted below; this is not a certification of every menu workflow.

Which versions this applies to

• Excel for Microsoft 365, Excel 2024, 2021, 2019, 2016 (Windows and Mac) and Excel for the web: Every formula in this tutorial uses MOD, ROUND, MIN, MAX and N, which exist in all of them.
• Google Sheets: MOD, ROUND, MIN, MAX and N take the same arguments in Sheets, so the formulas copy across as written. The main difference is formatting: for durations over 24 hours, use Format > Number > Duration (see how to sum time over 24 hours in Google Sheets).
• Regional settings: If your Excel uses a comma as the decimal separator, replace the commas between arguments with semicolons: =MOD(D2-C2;1)*24.

Why overnight shifts break the simple formula

Excel stores a time as a fraction of a day. 06:00 is 0.25, 12:00 is 0.5, 22:00 is about 0.9167. For a 22:00 to 06:00 shift:

0.25 - 0.9167 = -0.6667

Excel’s default date system can’t display a negative time, so a cell formatted as time shows #####. If the cell is formatted as a number you’ll see -16 hours (after *24), which is the gap between the two clock times in the wrong direction.

Excel doesn’t know the shift ended the next morning, because a time-only cell has no date. You have two ways to tell it:

1. Add one day when the end is earlier than the start (MOD, or the comparison trick). Fine for any shift shorter than 24 hours.
2. Store a date with each time. Required for shifts of 24 hours or longer.

Example data

Put this in A1:E8. Times are real times typed as 22:00 or 10:00 PM, not text.

Friday’s shift starts after midnight, so it doesn’t wrap at all. It’s in the table on purpose: a good formula must handle both kinds of row without special cases.

Step 1: Calculate the shift length with MOD

In F2, enter the formula and fill it down to F8:

=MOD(D2-C2,1)

MOD returns the remainder after dividing by 1. Microsoft’s MOD documentation states that the result has the same sign as the divisor (MOD(-3,2) returns 1), so a negative difference such as -0.6667 becomes 0.3333 (8 hours). A positive difference is already between 0 and 1, so same-day shifts pass through unchanged.

Format F2:F8 as h:mm (Home > Number Format > More Number Formats > Custom). Tuesday now shows 8:00.

If you find MOD hard to read, these two formulas return exactly the same values:

=IF(D2<C2,D2+1-C2,D2-C2)

=D2-C2+(D2<C2)

In the second one, D2<C2 returns TRUE (1) for overnight rows and FALSE (0) otherwise, so it adds one day only when needed. Pick one style and use it in the whole sheet.

Step 2: Subtract the break

There are 1,440 minutes in a day, so a 30-minute break is 30/1440 of a day:

=MOD(D2-C2,1)-E2/1440

If you record breaks as a time (0:30) instead of minutes, subtract the cell directly: =MOD(D2-C2,1)-E2.

Step 3: Convert to decimal hours for pay

Payroll usually needs 7.5, not 7:30. Multiply by 24:

=ROUND((MOD(D2-C2,1)-E2/1440)*24,2)

Why ROUND? Times are stored as binary fractions, so subtracting them can leave tiny remainders, for example 7.4999999999 instead of 7.5. You won’t see it with two decimals displayed, but a check like =F3=7.5 or a lookup against a rate table can fail. Rounding to two decimals removes the leftover.

To skip empty days (Sunday) without showing 0, add a blank check:

=IF(OR(C2="",D2=""),"",ROUND((MOD(D2-C2,1)-E2/1440)*24,2))

Expected results for the example:

Step 4: Total the week (and why the total shows 21:00)

If you total the decimal hours, =SUM(F2:F8) returns 45 and you’re done.

If you total durations formatted as h:mm, the total for this week shows 21:00 instead of 45:00. The value is correct (1.875 days), but the h code rolls over every 24 hours, the same way a clock does. Microsoft’s time-difference guide notes that with the standard time codes, hours never exceed 24. Use square brackets to show elapsed hours:

[h]:mm

Apply it to the total cell via Format Cells > Custom. The total now reads 45:00.

Step 5: Overtime and pay

With the weekly total in F9, a 40-hour threshold in B12 and an hourly rate in B13:

Regular hours:

=MIN(F9,$B$12)

Overtime hours:

=MAX(0,F9-$B$12)

Total pay at 1.5× overtime:

=MIN(F9,$B$12)*$B$13+MAX(0,F9-$B$12)*$B$13*1.5

For the example week: 40 regular hours, 5 overtime hours, and at a rate of 20 the pay is 800 + 150 = 950. Overtime rules vary by country, state and contract (daily thresholds, double time, weekend rules). This formula only models a weekly threshold, so check which rules apply to you.

Step 6 (optional): Split out night hours

Many contracts pay a premium for hours worked in a night window, such as 22:00 to 06:00. The night hours for a shift are the overlap between the shift and two windows: 22:00 to 06:00 the next morning, and 00:00 to 06:00 on the start day (for shifts that start after midnight, like Friday’s).

With the window start in B10 (22:00) and window end in B11 (6:00):

=ROUND((MAX(0,MIN(D2+(D2<C2),$B$11+1)-MAX(C2,$B$10))+MAX(0,MIN(D2+(D2<C2),$B$11)-C2))*24,2)

How to read it:

• D2+(D2<C2) is the end time, pushed into the next day for overnight rows.
• $B$11+1 is 06:00 the next day.
• Each MAX(0, MIN(end) - MAX(start)) measures one overlap and returns 0 if there’s none.

Expected night hours: Mon 0, Tue 8, Wed 6.5, Thu 4.25, Fri 5.5, Sat 1 (22:00–23:00). The formula assumes the night window crosses midnight and the shift is under 24 hours. It doesn’t know when the break was taken, so it doesn’t subtract it. If your breaks fall inside the night window, subtract them manually or add a “night break” column.

Shifts of 24 hours or longer: use dates

A time-only sheet can’t tell a 0-hour shift from a 24-hour one. 7:00 to 7:00 returns 0 with every formula above, and a 36-hour on-call shift returns 12. For these, record the date with each punch and subtract full date-times. No MOD needed:

=ROUND(((C2+D2)-(A2+B2))*24-E2/60,2)

Row 2 returns 23, row 3 returns 34.5. A negative result means someone forgot to move the end date to the next day. That’s worth flagging with a check column rather than wrapping in MOD, which would hide the mistake.

If your time clock exports date and time in one cell (2026-11-03 06:00), subtract the two cells directly: =(D2-C2)*24. If those exported values are left-aligned and the formula returns #VALUE!, they’re text. See Excel dates stored as text and convert numbers stored as text.

Rounding punches to the nearest 15 minutes

Some employers round clock-in and clock-out times. There are 96 quarter-hours in a day, so:

=ROUND(C2*96,0)/96

7:52 becomes 7:45, and 7:53 becomes 8:00. =MROUND(C2,TIME(0,15,0)) gives the same result. Round the punches before calculating hours, not the hours afterward. And check your local rules: rounding policies for wage-and-hour purposes are regulated in some places.

Excel vs. Google Sheets

Common mistakes

1. Using ABS(D2-C2) to “fix” negatives. It turns 22:00–06:00 into 16 hours, the time between the two clock readings in the wrong direction. Use MOD.
2. Formatting the total as h:mm. Anything over 24 hours rolls over. Use [h]:mm, or total the decimal hours instead.
3. Multiplying a [h]:mm duration by the hourly rate. 7:30 × 20 returns 6.25, because 7:30 is 0.3125 of a day. Convert to hours with *24 first.
4. Typing times as text. “10pm” without a space, or times pasted from a PDF, may stay as text. Test with =ISTEXT(C2). The free timesheet’s Check column flags these.
5. Using MOD for shifts of 24 hours or longer. MOD wraps everything into 0–24 hours. Store dates instead.

FAQ

What is the formula for overnight hours in Excel?

Use =MOD(End-Start,1)*24, for example =MOD(D2-C2,1)*24. MOD adds a day when the end time is earlier than the start time, so 22:00 to 06:00 returns 8 hours instead of a negative number.

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

The result is negative because the shift crossed midnight, and Excel’s default date system can’t display negative times. Wrap the subtraction in MOD: =MOD(D2-C2,1).

How do I subtract a 30-minute break from hours worked in Excel?

Divide the minutes by 1440 (minutes in a day) and subtract: =(MOD(D2-C2,1)-E2/1440)*24. If the break is entered as a time like 0:30, subtract the cell directly before multiplying by 24.

How do I calculate a shift longer than 24 hours?

Enter a date and time for both the start and the end, then use =((EndDate+EndTime)-(StartDate+StartTime))*24. Time-only formulas can’t tell 0 hours from 24 hours.

Does the MOD formula work in Google Sheets?

Yes. =MOD(D2-C2,1) works the same way in Google Sheets. For totals over 24 hours, format the cell as Duration.

Related tutorials

• How to sum time over 24 hours in Google Sheets
• Excel dates stored as text: how to convert them
• Convert numbers stored as text in Excel and Google Sheets
• COUNTIF vs COUNTIFS vs SUMPRODUCT (count shifts by day or by employee)

Download the free timesheet

Download hours-worked-overnight-timesheet.xlsx. It has four sheets:

• Timesheet: the example week above. Yellow cells are inputs (start, end, break, week start date, overtime threshold, rate, night window). Green cells are formulas: hours, [h]:mm duration, overnight flag, night hours, regular and overtime hours, and pay. A Check column flags times stored as text, start = end (possible 24-hour shift), and breaks longer than the shift.
• Blank template: the same layout with no data, ready to use.
• Long shifts: date + time entry for 24-hour and longer shifts, including one deliberately wrong row that shows the “end before start” warning.
• How it works: every formula with a plain-English explanation.

Start and end cells have data validation that only accepts a time of day. Calculated values were verified by recalculating the file in LibreOffice Calc 25.2.

Live-test findings

The six overnight shift results were 8, 7.5, 7.5, 8, 7.5 and 6.5 hours in Excel for Mac: 45 hours in total and 950 pay. The existing downloadable timesheet’s calculations were also checked in Excel. Sheets formula calculations passed; this does not certify every data-validation interaction.

Sources

• Microsoft Support: Calculate the difference between two times in Excel (subtracting times, custom h:mm format, hours never exceed 24 with standard time codes, converting with *24 / *1440): https://support.microsoft.com/en-us/excel/calculate-the-difference-between-two-times-in-excel
• Microsoft Support: MOD function (result has the same sign as the divisor): https://support.microsoft.com/en-us/excel/functions/mod-function
• Google Docs Editors Help: MOD (same syntax in Google Sheets): https://support.google.com/docs/answer/3093497
• Microsoft Q&A: Subtract times to get a decimal (crossing midnight gives a negative value; the (B1<A1) adds one day): https://learn.microsoft.com/en-us/answers/questions/4792204/subtract-times-to-get-a-decimal
• LibreOffice Calc 25.2: used to recalculate every formula and the example workbook (no web source; local verification).