Calculate Hours Worked in Excel or Google Sheets: Dates, Breaks and Overnight Shifts

Open the calculator

Build the explicit-date model

The most common error is storing only 09:00 and 17:00 without dates. That works until a shift crosses midnight, spans multiple days, or is entered out of order. Separate dates make the arithmetic unambiguous.

Use one row per shift. Keep dates and times in separate columns so entries are easy to check, sort, filter, and audit.

Column map

Use this layout from row 2 downward:

• A2: Start date, for example 2026-10-05
• B2: Start time, for example 09:00
• C2: End date, for example 2026-10-05
• D2: End time, for example 17:00
• E2: Unpaid break minutes as a number, for example 30
• F2: Net duration: =(C2+D2)-(A2+B2)-E2/1440
• G2: Decimal hours: =F2*24

A2 and C2 must be real dates. B2 and D2 must be real times. E2 must be a plain number of minutes, not 00:30 and not the text 30 min. F2 must remain numeric duration. G2 must remain numeric hours.

In Excel, dates are whole numbers and times are fractions of a day. The same is true in Google Sheets. Adding C2+D2 creates a complete end timestamp. Adding A2+B2 creates a complete start timestamp. Their difference is elapsed days. Dividing E2 by 1440 converts break minutes to days because one day has 1440 minutes. Subtracting that break leaves net paid time as a duration.

Formulas to copy

For row 2:

Duration in F2:

=(C2+D2)-(A2+B2)-E2/1440

Decimal hours in G2:

=F2*24

For a weekly duration total, if shifts are in rows 2 through 6, use:

=SUM(F2:F6)

Format that total cell as [h]:mm.

For a weekly decimal-hours total, use:

=SUM(G2:G6)

or:

=SUM(F2:F6)*24

The second form is preferable because it adds exact durations before converting to decimal. Only round at the end. For payroll display rounded to two decimals, use:

=ROUND(SUM(F2:F6)*24,2)

Do not round every daily cell and then add the rounded values if exact payroll hours matter. Small daily display rounding can accumulate across a week. Add exact values, then round once.

Format durations so totals stay correct

A duration formatted as h:mm shows clock time within one day. That format will display a 37.5-hour weekly total as 13:30, which is wrong for hours worked. The square brackets in [h]:mm tell Excel and Google Sheets to accumulate hours beyond 24.

Apply [h]:mm to:

• Every daily duration cell in column F
• The duration total cell

Apply Number format with two decimals to:

• Every decimal-hours cell in column G
• The decimal-hours total cell

If F2 shows ########, the column is too narrow or the result is negative. Widen the column first. If it remains ######## in Excel, the calculation produced a negative duration, usually because the end timestamp is earlier than the start timestamp or the break is larger than the shift.

Worked examples

Same-day shift

• A2: 2026-10-05
• B2: 09:00
• C2: 2026-10-05
• D2: 17:00
• E2: 30

Calculation:

End timestamp minus start timestamp is 8 hours. Subtract 30 minutes. F2 is 7:30. G2 is 7.50.

Overnight shift

• A2: 2026-10-05
• B2: 22:00
• C2: 2026-10-06
• D2: 06:00
• E2: 30

The end date must be the next date. End timestamp minus start timestamp is 8 hours. Subtract 30 minutes. F2 is 7:30. G2 is 7.50.

This is why the end date matters. Without C2, the sheet cannot distinguish 22:00 to 06:00 next morning from a reversed or mistaken entry.

Weekly total

If F2:F6 each contain 7:30, then:

=SUM(F2:F6)

formatted as [h]:mm displays:

37:30

The same cells formatted as h:mm would display 13:30 because the display discards complete 24-hour cycles. The underlying value is still 37.5 hours, but the displayed text is misleading. Always use [h]:mm for worked-time totals.

For decimal payroll hours:

=SUM(F2:F6)*24

returns 37.5. Display it as 37.50 with Number format.

Time-only alternative and its limitations

If dates are genuinely unavailable, this time-only formula is sometimes used:

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

where B2 is start time, D2 is end time, and E2 is unpaid break minutes.

State its limitations clearly. It assumes at most one midnight crossing. For example, it treats 22:00 to 06:00 as 8 hours because MOD wraps the negative time difference forward by one day. But it cannot validate whether the record is genuinely overnight or simply reversed. It cannot measure a multi-day shift because there is no date information. A 30-hour elapsed period and a 6-hour overnight period can look identical if only times are stored.

Use time-only calculation only for simple same-day or single-overnight schedules where another system already guarantees the correct date. For attendance records, payroll, multi-day assignments, or any data that may contain entry errors, use the explicit-date formula. The explicit-date version can expose an end timestamp earlier than the start timestamp as a negative result that needs correction. The time-only version hides that problem by wrapping it into a positive duration.

The time-only alternative also cannot measure real daylight saving changes without offsets or zone handling. The same limitation applies to the explicit-date model when it contains only local dates and times.

Daylight saving requires more than local timestamps

Spreadsheet local timestamps do not know IANA daylight saving rules automatically. A cell containing 2026-03-29 02:30 does not by itself know whether that time existed, was skipped, repeated, or shifted in a particular zone.

In most ordinary weekly timesheets this does not change the result because start and end timestamps are entered as observed local times. But do not claim that subtracting local timestamps measures true elapsed physical time across a spring-forward or fall-back transition. A nominal 8-hour overnight shift can be 7 or 9 physical hours when clocks change, depending on zone and transition.

To handle that correctly you need explicit UTC offsets or zone-aware timestamps, not bare local dates and times. Store the zone and offset, convert both timestamps to a common absolute reference, then subtract. If your sheet has only local dates and times, treat the result as nominal scheduled hours, not as a daylight-saving-aware elapsed-time measurement.

Keep duration cells numeric

Keep F2 numeric. Do not convert it to text for display unless you need a text label in a separate column.

TEXT results are display strings. For example:

=TEXT(F2,"h:mm")

may look correct, but the result cannot be summed normally. A SUM over text values ignores them or produces zero. Likewise, HOUR alone loses whole days. HOUR(F2) returns only the hour component within a day, so a 37.5-hour total would report the hour part incorrectly for payroll.

Correct workflow:

1. Calculate numeric duration in F2.
2. Format F2 as [h]:mm.
3. Calculate decimal hours elsewhere as =F2*24.
4. Sum numeric durations.
5. Format or round only the final display.

If you need both a duration-style weekly total and a decimal payroll total, keep both. Use =SUM(F2:F6) formatted as [h]:mm for human review and =SUM(F2:F6)*24 as a number for multiplication by an hourly rate.

Verification table

Enter these rows and check F as [h]:mm and G as Number:

The last row tests a multi-day duration. Time-only subtraction cannot represent it correctly. The explicit-date result is 48 hours minus 1 hour break, shown as 47:00.

Check the weekly duration with =SUM(F2:F6) formatted as [h]:mm. Check decimal hours with =SUM(F2:F6)*24.

Debugging checklist

1. Negative or ######## result: Compare complete timestamps. C2+D2 must be later than A2+B2. For overnight work, C2 must be the next date. Check whether E2 is larger than the shift itself.
2. Break larger than shift: An unpaid break cannot exceed gross shift length. A 20-minute shift with 30 minutes break produces negative paid time. Validate with =IF(E2/1440>(C2+D2)-(A2+B2),"Check break", (C2+D2)-(A2+B2)-E2/1440).
3. Pasted text instead of real dates: Pasted exports often look like dates but are stored as text. Check with ISNUMBER(A2) and ISNUMBER(B2). FALSE means the value is text. Re-enter it, use DateValue/TimeValue conversion, or use Paste Special and date parsing rather than keeping text.
4. Date locale mismatch: 10/06/2026 can mean 10 June or October 6 depending on locale. Use unambiguous entry such as 2026-10-06 or verify how the sheet interprets day and month. Sort by date to expose swapped values.
5. Formula separator differences: Some locales use semicolons instead of commas. If =(C2+D2)-(A2+B2)-E2/1440 needs adjustment, the arithmetic is unchanged; only argument separators in functions change. For example, ROUND may require =ROUND(SUM(F2:F6)*24;2) in those locales.
6. Time entered as decimal or text: 7.5 is not the same as 7:30 in a time cell. 09:00 must be a time value. 30 in E2 must be minutes as a number. Do not put 00:30 in E2 unless the formula is changed to subtract a time value directly.
7. Wrong display format: If totals show 13:30 instead of 37:30, change the format from h:mm to [h]:mm. If decimals show dates, change the format from Date to Number.
8. Hidden seconds: Times may contain seconds that change rounding. Format temporarily as hh:mm:ss to inspect, then return to the required display format.
9. Blank cells treated as zero: A blank end date can make a shift appear very negative. Require all four timestamp cells before calculating, for example with =IF(COUNT(A2:D2)<4,"Incomplete",(C2+D2)-(A2+B2)-E2/1440).

Sources and rule references

• 29 CFR 785.18: short rest periods
• 29 CFR 785.19: meal periods
• 29 CFR 778.104: each workweek stands alone
• 29 CFR 778.105: defining the workweek

Related reading

• How to Calculate Hours Worked With Lunch Breaks
• Paid Breaks vs Unpaid Lunch: What Counts in Your Work Hours?