How to Calculate Hours Worked in Excel (Including Overnight Shifts)
Every answer to this question starts with "put the start time in B2 and the end time in C2". Real rotas do not look like that. They have one cell per shift reading 9:00-17:30, or 9am–5pm, or Mon 08:00 - 16:30 — and the hour of splitting that text by hand is where the mistakes come from.
Last updated: October 2026
| Shift | Start | End | Hours | |
|---|---|---|---|---|
| 9:00-17:30 | → | 09:00 | 17:30 | 8.50 |
| 22:00-06:00 | → | 22:00 | 06:00 | 8.00 |
| 9am–5pm | → | 09:00 | 17:00 | 8.00 |
Method 1 — formulas, once the times are already split
- Same-day shift:
=(C2-B2)*24gives decimal hours. - Crossing midnight:
=MOD(C2-B2,1)*24— without MOD, 22:00 to 06:00 comes out as −16. - Unpaid break: subtract it as a fraction of an hour, e.g.
=MOD(C2-B2,1)*24-0.5for thirty minutes. - All of which assumes B2 and C2 are real times. If the shift is one piece of text, you first need
TIMEVALUE(LEFT(A2,FIND("-",A2)-1))and a matchingMID— which break on an en dash, on "9am", on a day label, and on a space in the wrong place.
Method 2 — the 1-click way (Tellsheet)
Hours Worked
Select the shift column and run Hours Worked. It adds Start, End and Hours, reading the shift however it was written: hyphen, en dash, em dash or the word "to"; 24-hour, 9am/5pm, the British 9.30 dot, or four-digit 0930; a day label in front and a note behind. It crosses midnight correctly, deducts an unpaid break — the same for everyone, or per row from a column — and reads separate In/Out columns if that is the shape your sheet is in. Start and End are written as real Excel times, so they sort and chart; the Hours column is a plain decimal, which is what payroll wants.
The things it will not quietly get wrong
- "9-5" is not twenty hours. Read literally, 9:00 to 5:00 crosses midnight. When neither side says am or pm and both sit on the morning half of the clock, the afternoon end is meant — and the summary tells you how many rows were read that way.
- An overnight end belongs to the next day. So
End − Startin your own formulas still gives a positive span, not a negative one. - A break longer than the shift is held at zero, not turned into negative pay, and is reported.
- A shift it cannot read is left blank and counted — never approximated. A wrong number of hours is a wrong wage; a blank cell is at least visible.
Frequently asked questions
What's the formula for hours worked?
=(end-start)*24, or =MOD(end-start,1)*24 across midnight.
My shift is all in one cell.
No formula reads that directly — Hours Worked does.
Why is my overnight shift negative?
06:00 is numerically less than 22:00. MOD fixes it; so does this tool, automatically.
Can I get h:mm instead of decimal hours?
Yes — run Duration Converter on the Hours column afterwards.
Related Excel guides
A rota column into a payroll column
Hours Worked reads the shift however it was typed — overnight, breaks and all — with a preview before anything changes.
Get Tellsheet free See pricing