Hours Calculator

How to calculate hours worked in Excel: 4 formulas that work

Quick answer

Here is how to calculate hours worked in Excel: put the start time in B2 and the end time in C2, both formatted as time, then use =(C2-B2)*24 for decimal hours. Subtract lunch with =(C2-B2-D2)*24, wrap overnight shifts in MOD, and format weekly totals as [h]:mm.

Updated 2 min readGambit Studio

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

CaseFormulaExample result
Hours between two times=(C2-B2)*248:00 to 16:30 gives 8.5
Minus an unpaid lunch in D2 (0:30)=(C2-B2-D2)*248:00 to 16:30 minus 0:30 gives 8
Shift past midnight=MOD(C2-B2,1)*2422:00 to 06:00 gives 8
Overtime over 8 a day=MAX(0,E2-8) with E2 = daily hours9.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

  1. Columns: A date, B in, C out, D unpaid lunch, E hours.
  2. Format B, C and D as Time (for example 13:30) and E as Number with 2 decimals.
  3. Type times with a colon (8:00, 16:30) or with a space and am/pm (8:00 am).
  4. In E2 type =MOD(C2-B2,1)*24-D2*24; it works for day and overnight shifts.
  5. 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.

Sources

  1. Microsoft Support: Add or subtract time in Excel

Frequently asked questions

What people ask most about this, answered with the source.

What is the Excel formula for hours worked?
=(End-Start)*24, for example =(C2-B2)*24, with both cells formatted as time. The result is decimal hours.
How do I subtract lunch in Excel?
Put the lunch length in its own cell as a time (0:30) and use =(C2-B2-D2)*24.
How do I calculate hours in Excel when the shift goes past midnight?
Use =MOD(C2-B2,1)*24. MOD adds a day when the end time is earlier than the start time.
Why does Excel show 18:00 instead of 42 hours?
The cell is formatted as clock time. Change it to the custom format [h]:mm.
HorasComing soon to the App Store
Notify me at launch