Adding up a time card looks simple until breaks, minutes and overnight shifts show up. This guide shows the formula, a worked example, the Excel version and the mistakes that cause payroll errors.
Updated November 2026
The basic formula
Hours worked in a shift equal the clock-out time minus the clock-in time, minus any unpaid break. Payroll systems and spreadsheets work in decimal hours, so you also need to convert minutes: divide by 60. A 30-minute break is 0.5 hours and a 45-minute break is 0.75 hours.
If you want the answer without doing the math, enter your times in our time card calculator. It also adds up the week and prices overtime.
Worked example
Suppose you clock in at 9:00 am and out at 5:30 pm with a 30-minute unpaid lunch. From 9:00 to 17:30 is 8 hours 30 minutes. Subtract 30 minutes and you have 8 hours, or 8.0 in decimal form. Over five days that is 40 hours.
Now a shift with odd minutes: in at 8:45 am, out at 5:15 pm, with a 45-minute break. From 8:45 to 17:15 is 8 hours 30 minutes, which is 8.5 hours. Subtract 0.75 hours and you worked 7.75 hours, which is 7 hours 45 minutes.
Shifts that cross midnight
When the clock-out time is earlier than the clock-in time, as in a 10 pm to 6 am shift, subtracting gives a negative number. Add 24 hours to the end time first. In Excel the MOD function does this for you, as described in Microsoft’s page on the MOD function.
Excel formulas
Put clock-in times in column B, clock-out times in column C and the unpaid break in minutes in column D, all as real Excel times and numbers. Then daily hours are:
=MOD(C2-B2,1)*24-D2/60
For the first example, 17:30 – 9:00 is 0.3541667 of a day. Multiplied by 24 it is 8.5 hours, and subtracting 30/60 leaves 8. Weekly total in E7 is =SUM(E2:E6). To split regular and overtime hours at 40 and price overtime at 1.5 times a $20 rate:
=MIN(E7,40)*20+MAX(E7-40,0)*20*1.5
A week of five 10-hour days is 50 hours. The pay is 40 x $20 + 10 x $20 x 1.5, which is $800 + $300 = $1,100.00. Our timesheet template has these formulas built in, and the overtime calculator handles different multipliers.
Rounding rules
Some employers round punches to the nearest quarter hour or tenth of an hour. If yours does, use =MROUND(C2-B2,"0:15") on the times before you convert them. Rounding is allowed only if it does not shortchange employees over time, so follow your payroll policy and local rules rather than a spreadsheet default.
Paid and unpaid breaks
Short rest breaks are usually counted as hours worked, while meal periods can be unpaid if the employee is fully relieved of duty. The details are in the federal regulation on hours worked, 29 CFR Part 785, and state rules can be stricter. When in doubt, ask your payroll provider. Enter only the unpaid minutes in the break column.
Common mistakes
- Entering 8:30 as 8.30 hours. Eight hours 30 minutes is 8.5 hours.
- Forgetting to subtract an unpaid break.
- Typing times as text, so Excel cannot subtract them. Check that they right-align in the cell.
- Applying overtime per day when your rules apply it per week, or the reverse.
- Not accounting for midnight crossings.
For pay after taxes use the paycheck calculator.
Frequently asked questions
How do I calculate hours worked?
Subtract the clock-in time from the clock-out time, then subtract any unpaid break. A 9:00 to 17:30 shift with a 30-minute break is 8 hours.
How do I turn minutes into decimal hours?
Divide minutes by 60. Fifteen minutes is 0.25 hours, 30 is 0.5 and 45 is 0.75.
How do I calculate hours in Excel when a shift crosses midnight?
Use =MOD(out-in,1)*24 to get hours, then subtract the break in hours.
Are lunch breaks paid?
It depends on length and whether you are fully relieved of duty. Federal rules are in 29 CFR Part 785 and state laws may add requirements.