How to calculate hours worked in Excel
Excel stores times as parts of a day, so 12:00 is 0.5. Subtracting the start from the end gives a part of a day, and multiplying by 24 turns it into hours.
For shifts past midnight, the end is earlier than the start. MOD(end − start, 1) wraps it round correctly.
The formula
| A | B | |
|---|---|---|
| 1 | Start | End |
| 2 | 9:00 | 17:15 |
=(B2-A2)*24Result: 8.25
9:00 to 17:15 is 8.25 hours, ready to multiply by a pay rate.
Step by step
- Type =(B2-A2)*24.
- Press Enter and fill down.
- Format the result as a number, not a time.
Other ways to write it
=MOD(B2-A2,1)*24Also works for night shifts that end the next day.
Each of these gives the same result on the example above, in Excel and in Cellwise.
Common mistakes
- Forgetting × 24
- B2-A2 alone gives 0.34, which is a fraction of a day.
- Negative night shifts
- A 22:00 to 06:00 shift gives a negative number without MOD.
Reading isn’t the same as doing
Times and timesheets gives you real tasks on this, and tests your formulas on data you haven’t seen. It’s free, with no sign-up.