Why does my hours cell show ##### or a negative time?

##### has two causes. The column may simply be too narrow — widen it. But in a [h]:mm-formatted cell, ##### also appears when an overnight shift makes the result negative, because Excel can't display a negative time at all; widening the column won't fix that one — use =MOD(E2-B2,1) so the duration wraps past midnight. You only ever see a literal negative number on screen when the cell is formatted as a plain number rather than as a time.

Should I send payroll decimal hours or h:mm?

Send decimal hours. Multiply the total by 24 with =F2*24 and format it as a number, so 8:15 becomes 8.25. Payroll multiplies hours by an hourly rate, and that only works on a plain decimal, not a clock-time value.

How do I round hours to the nearest quarter hour?

Round the decimal total to the nearest 0.25 with =MROUND(F2*24,0.25). That snaps each entry to :00, :15, :30, or :45, matching common quarter-hour payroll rules. Use 0.1 instead if your employer rounds to tenths of an hour.

Does Excel understand AM/PM when I type times?

Yes — type 9:00 AM or 5:30 PM with a space before AM/PM and Excel stores a real time value. Skip the AM/PM and it assumes 24-hour time, so 17:30 also works. Either way the subtraction formulas behave identically.

Why do I multiply by 24 to get decimal hours?

Excel stores any time as a fraction of a 24-hour day, so 12 hours is 0.5 and a full day is 1. Multiplying by 24 converts that fraction back into hours — that's why =F2*24 turns 0.5 into 12.

How to Calculate Hours Worked in Excel

Last updated June 2026

Quick answer

To calculate hours worked in Excel, enter start and end times as real times, then subtract: =(E2-B2)-(D2-C2) takes end minus start and removes the lunch break. Format that cell as [h]:mm so totals over 24 hours still show. For decimal hours for payroll, multiply the result by 24 with =F2*24 and format it as a plain number.

How do you calculate hours worked with a lunch break?

Lay out the columns
This teaching example has no header row — your data goes straight into row 2. Type your start time in B2, lunch-out in C2, lunch-in in D2, and end time in E2; the result lives in F2. Type times the way Excel reads them — 9:00 AM, 12:30 PM — and it will store real time values you can do math on.
Subtract start, end, and lunch in one formula
In F2, take end minus start, then subtract the lunch gap (Lunch In minus Lunch Out). Copy the formula down the column for every row.
=(E2-B2)-(D2-C2)
Format the result as [h]:mm
Select F2, press Ctrl+1, choose Custom, and enter [h]:mm. The square brackets let the cell show totals past 24 hours instead of rolling over to a clock time.
Total the week
Below your last day, sum the Hours column with =SUM(F2:F8). Keep this total cell on the same [h]:mm custom format so a 42-hour week reads as 42:00, not 18:00.
=SUM(F2:F8)
Convert to decimal hours for payroll
In a new cell, multiply the total by 24, then format that cell as Number with two decimals. 8:30 becomes 8.50 — the form most payroll systems and hourly-rate math expect.
=F2*24

Format / formula	Displays	Use it for
`[h]:mm`	`8:30`	Daily and weekly totals that can exceed 24 hours
`h:mm`	`8:30`	Single durations under 24 hours; rolls over above that
`=F2*24` as `0.00` (Number)	`8.50`	Payroll, multiplying hours by an hourly rate

An 8h30m shift shown three ways. The first two rows reformat the same stored value (`0.354`, a fraction of one day). The decimal column is different: it comes from multiplying that value by 24 with `=F2*24`, not from reformatting the time cell — formatting the time cell itself as Number would show `0.35`, not `8.50`.

Time is a fraction of a 24-hour day, so 0.5 equals 12 hours and 1 equals a full 24. That's exactly why decimal hours come from multiplying by 24: =F2*24 turns the stored fraction back into hours.

How do you handle an overnight shift?

Use MOD when End is earlier than Start
A shift from 10:00 PM to 6:00 AM makes plain subtraction go negative. Wrap it in MOD with a divisor of 1 to wrap the time across midnight and return a correct 8 hours.
=MOD(E2-B2,1)
Add lunch back into the overnight version
Subtract the break from the wrapped result so overnight rows match your daytime formula. This is the formula our timesheet uses in every Hours cell.
=MOD(E2-B2,1)-(D2-C2)
Keep the same [h]:mm format
Overnight results are still durations, so format the cell as [h]:mm like every other row. An 11:00 PM to 7:30 AM shift with a 30-minute lunch then reads as 8:00, and it sums cleanly into the weekly total.

Frequently asked questions

Why does my hours cell show ##### or a negative time?: ##### has two causes. The column may simply be too narrow — widen it. But in a [h]:mm-formatted cell, ##### also appears when an overnight shift makes the result negative, because Excel can't display a negative time at all; widening the column won't fix that one — use =MOD(E2-B2,1) so the duration wraps past midnight. You only ever see a literal negative number on screen when the cell is formatted as a plain number rather than as a time.
Should I send payroll decimal hours or h:mm?: Send decimal hours. Multiply the total by 24 with =F2*24 and format it as a number, so 8:15 becomes 8.25. Payroll multiplies hours by an hourly rate, and that only works on a plain decimal, not a clock-time value.
How do I round hours to the nearest quarter hour?: Round the decimal total to the nearest 0.25 with =MROUND(F2*24,0.25). That snaps each entry to :00, :15, :30, or :45, matching common quarter-hour payroll rules. Use 0.1 instead if your employer rounds to tenths of an hour.
Does Excel understand AM/PM when I type times?: Yes — type 9:00 AM or 5:30 PM with a space before AM/PM and Excel stores a real time value. Skip the AM/PM and it assumes 24-hour time, so 17:30 also works. Either way the subtraction formulas behave identically.
Why do I multiply by 24 to get decimal hours?: Excel stores any time as a fraction of a 24-hour day, so 12 hours is 0.5 and a full day is 1. Multiplying by 24 converts that fraction back into hours — that's why =F2*24 turns 0.5 into 12.