Excel stores time as a fraction of a day (12:00 is 0.5), which is why the difference is multiplied by 24 to get hours. Microsoft's own guide covers adding and subtracting time this way (Microsoft Support).
The 4 formulas
| Case | Formula | Example result |
|---|---|---|
| Hours between two times | =(C2-B2)*24 | 8:00 to 16:30 gives 8.5 |
| Minus an unpaid lunch in D2 (0:30) | =(C2-B2-D2)*24 | 8:00 to 16:30 minus 0:30 gives 8 |
| Shift past midnight | =MOD(C2-B2,1)*24 | 22:00 to 06:00 gives 8 |
| Overtime over 8 a day | =MAX(0,E2-8) with E2 = daily hours | 9.5 hours gives 1.5 |
For the week, add the daily hours with =SUM(E2:E8). If you keep the totals as time instead of decimals, format the cell as [h]:mm, otherwise Excel shows 42 hours as 18:00. Remember that federal overtime is per workweek over 40, not per day; daily overtime only applies where a state requires it (how to calculate overtime pay).
Set up the sheet in 5 steps
- Columns: A date, B in, C out, D unpaid lunch, E hours.
- Format B, C and D as Time (for example 13:30) and E as Number with 2 decimals.
- Type times with a colon (8:00, 16:30) or with a space and am/pm (8:00 am).
- In E2 type
=MOD(C2-B2,1)*24-D2*24; it works for day and overnight shifts. - Copy E2 down and add a weekly
=SUM(); multiply by your hourly rate for gross pay.
Common errors
- #VALUE!: a time was typed as text (for example "8.30"). Retype it as 8:30.
- Negative or ##### result: the shift crosses midnight; use the MOD version.
- Total resets after 24 hours: format the total as [h]:mm.
The hours app we're building
Hours Calculator: Time Card is an app Gambit Studio is building and it is not on the App Store yet. It will log clock-in, clock-out and breaks for each day, total the week and the month, keep an hour bank against your contracted schedule, show overtime with the estimated pay from your hourly rate and export a PDF time card, with no account and offline. Leave your email and we will tell you when it lands on the App Store.