Home / Blog / Calculate Hours Worked in Excel

How to Calculate Hours Worked in Excel

By Frame Sixty Labs · Last updated: September 21, 2026

Why does Excel get time wrong in the first place?

Because Excel doesn’t store 8:30 as “eight and a half hours.” It stores every time as a fraction of one 24-hour day. Midday is 0.5. Six in the morning is 0.25. A shift of 8 hours 30 minutes is 8.5 ÷ 24 = 0.354166…

That single design decision causes every Excel timesheet problem you will ever hit:

• Subtract a later time from an earlier one and you get a negative fraction, which Excel can’t show as a time.
• Add up more than a day’s worth and the display wraps at 24 hours, because it is still formatted as a time of day.
• The number in the cell is a fraction of a day, so multiplying it by an hourly rate produces nonsense until you convert it.

None of that is a bug. It just means a timesheet needs two formulas and two number formats that most people never apply.

How do I set up an hours-worked timesheet in Excel?

Five columns is all it takes. Work through these steps once and the sheet is reusable every week.

1. Lay out the columns. A Day, B In, C Out, D Unpaid break, E Hours. Data starts in row 2.
2. Enter times with a colon — 8:00, 16:30, or 8:00 AM. Typing 800 stores the number eight hundred, and typing 8.5 stores the number 8.5, neither of which is a time.
3. Enter breaks as durations too: a 30-minute break is 0:30, not 30.
4. Put the day’s formula in E2: =MOD(C2−B2,1)−D2, then fill it down the column.
5. Format E2:E6 as h:mm. Select the cells, open Format Cells (Ctrl+1, or ⌘+1 on a Mac), choose Custom, and type h:mm.
6. Total the week in E7 with =SUM(E2:E6), and format that one cell as Custom [h]:mm — with the square brackets.
7. Add a decimal cell: =E7*24, formatted as Number with two decimals.
8. Add a pay cell: =E7*24*22 for a $22.00 rate, formatted as Currency.

What formula calculates hours worked with a break?

The one to use is:

=MOD(C2−B2,1)−D2

The plain version, =C2−B2−D2, gives the same answer for any shift that starts and ends on the same calendar day. It only fails at midnight — but it fails silently on the day it matters, so there is no reason to prefer it.

Worked through: clock in 9:00, clock out 17:45, unpaid break 0:45. Excel computes 0.739583 − 0.375 = 0.364583 (that’s 8:45), then subtracts 0.03125 (that’s 0:45) to leave 0.333333 — exactly one third of a day, or 8:00. You never see those decimals; you just see 8:00 once the cell is formatted h:mm.

How do I calculate an overnight shift without an error?

A night shift from 22:00 to 06:30 is where plain subtraction collapses. Excel reads it as 0.270833 − 0.916666 = −0.645833, a negative time, and fills the cell with #####.

MOD fixes it: MOD(−0.645833, 1) adds a whole day back and returns 0.354166 — 8:30, which is correct. The formula reads as “if the clock-out landed before the clock-in, it was the next day.”

One limit worth knowing: MOD assumes a shift is under 24 hours. That is true of every real shift, but it means a 26-hour on-call block entered as two punches would come back as 2:00. Split anything that long into two rows.

Why does my weekly total reset at 24 hours?

Because h:mm is a time-of-day format, and a clock rolls over at midnight. Sum a week of 36 hours 15 minutes in a cell formatted h:mm and Excel shows 12:15: it subtracted a full day, exactly as a clock would.

The value in the cell was never wrong. Format the total as Custom [h]:mm and the same cell reads 36:15. The square brackets mean “let hours run past 24.” Use [h]:mm on every subtotal, every weekly total, and any monthly or pay-period total.

This is the single most expensive Excel timesheet mistake, because 12:15 is a perfectly plausible-looking number. Nothing on screen says a day went missing.

How do I convert the total to decimal hours and pay?

Payroll wants decimal hours, not H:MM. Since Excel holds a duration as a fraction of a day, multiplying by 24 gives hours:

So =E7*24 converts, and =E7*24*22 pays it out. There is one catch: a cell that inherits a time format will display 36.25 as a time of day instead of a number. Set that cell to Number (or General) and it shows 36.25.

If you need to round to quarter hours, =MROUND(E2,"0:15") rounds a duration to the nearest 15 minutes — 8:07 becomes 8:00, 8:08 becomes 8:15. Round the individual days or the total consistently, never both, and see timesheet rounding rules for when rounding is fair and when it isn’t.

A full worked week

Five days, one of them overnight, one short day, a $22.00 hourly rate:

Thursday is the overnight row: 22:00 to 6:30 is 8:30 of clock time, less a 0:30 break, so 8:00. Friday is a half day, 8:00 to 12:15, no break, 4:15.

The week is 8:00 + 8:00 + 8:00 + 8:00 + 4:15 = 36:15. In decimal that is 36 + 15÷60 = 36.25 hours, and at $22.00 an hour, 36.25 × 22 = $797.50.

Now look at what a broken sheet would have shown for the same week. Without MOD, Thursday is ##### and the week is short. Without [h]:mm, the total reads 12:15. And if someone reads 36:15 off the screen and types 36.15 into payroll, the pay comes out $795.30 — $2.20 light, every week, from a typo that looks like a transcription.

Why is my Excel timesheet total wrong?

Four symptoms cover nearly every case:

One more that hides well: a time pasted in as text sits left-aligned in the cell and is skipped by SUM entirely. Real times align right by default. If a column looks left-aligned, retype one entry and watch whether it jumps.

Does any of this change in Google Sheets?

Barely. MOD, SUM and multiplying by 24 work identically, and the [h]:mm format exists under Format → Number → Custom date and time. The one difference: Sheets will display a negative duration such as −15:30 instead of #####, which is friendlier but still wrong for an overnight shift. Keep MOD.

When is a spreadsheet the wrong tool?

A spreadsheet is excellent when you need a record: a file to keep, formulas to audit, a column to hand to a bookkeeper. It is a poor fit when you are standing in a car park at the end of a shift trying to work out whether you hit 40 hours. Opening Excel on a phone to re-check the format of one cell is not a workflow.

The split most people land on: add the hours on the phone as the week happens, and keep the spreadsheet for the record at the end of it.

Frequently asked questions

What is the formula to calculate hours worked in Excel?

=MOD(C2−B2,1)−D2, with the cell formatted h:mm. Plain =C2−B2 only works when the shift doesn’t cross midnight.

Why does Excel show ##### instead of hours worked?

The formula produced a negative time, which Excel can’t display in the standard 1900 date system — typically an overnight shift, or a break typed as 30 rather than 0:30. A column that is merely too narrow also shows #####, so widen it to rule that out first.

Why does my Excel weekly total reset at 24 hours?

The total cell is formatted h:mm, a time of day, so it wraps at midnight and 36:15 appears as 12:15. Change the format to [h]:mm. The stored value was correct all along.

How do I convert Excel hours to decimal?

Multiply by 24: =E7*24 turns 36:15 into 36.25. Never retype the digits — 36:15 is 36.25, not 36.15.

How do I calculate pay from hours in Excel?

=E7*24*22 gives $797.50 for 36:15 at $22.00 an hour. Format the cell as Currency, and always multiply the decimal figure rather than the H:MM cell.

Do these formulas work in Google Sheets?

Yes — MOD, SUM, ×24 and [h]:mm all behave the same way.