How to Calculate Hours Worked (With Breaks, Decimals and Excel)
Quick answer: hours worked = end time − start time − unpaid breaks. Convert the minutes to decimals (minutes ÷ 60) for payroll. Example: 9:00 AM to 5:30 PM with a 30-minute lunch = 8.00 hours.
Whether you're filling in a timesheet, checking a payslip or running payroll for a small team, calculating hours by hand is surprisingly easy to get wrong — especially with lunch breaks, minutes and overnight shifts. Here's a simple, reliable method.
Step 1: Work out each day's hours
- Write the start and end times in 24-hour format if you can (5:30 PM = 17:30). It avoids AM/PM mistakes.
- Subtract: 17:30 − 9:00 = 8 hours 30 minutes.
- Subtract unpaid breaks: 8 h 30 min − 30 min = 8 hours.
Or let the time calculator do it — enter the start, end and break, and add an hourly rate to see the pay:
Calculate the hours and minutes between two times, and add or subtract durations.
Step 2: Convert minutes to decimal hours
Payroll systems and spreadsheets use decimal hours, where 7 hours 45 minutes is 7.75 — not 7.45. Divide the minutes by 60:
| Minutes | Decimal | Minutes | Decimal |
|---|---|---|---|
| 5 | 0.08 | 35 | 0.58 |
| 10 | 0.17 | 40 | 0.67 |
| 15 | 0.25 | 45 | 0.75 |
| 20 | 0.33 | 50 | 0.83 |
| 25 | 0.42 | 55 | 0.92 |
| 30 | 0.50 | 60 | 1.00 |
Convert hours and minutes to decimal hours for timesheets and payroll — and decimal hours back to hours and minutes — with an optional pay calculation.
Step 3: Total the week
| Day | Start | End | Unpaid break | Hours |
|---|---|---|---|---|
| Monday | 9:00 | 17:30 | 30 min | 8.00 |
| Tuesday | 8:45 | 17:15 | 45 min | 7.75 |
| Wednesday | 9:00 | 18:00 | 60 min | 8.00 |
| Thursday | 9:15 | 17:30 | 30 min | 7.75 |
| Friday | 9:00 | 15:00 | none | 6.00 |
| Total | 37.50 | |||
At an hourly rate of 20, that week's pay is 37.5 × 20 = 750. The time calculator's "total of many durations" mode adds up a week for you: enter each day as 8:00, 7:45 and so on.
Overnight shifts
When a shift crosses midnight, the end time is "smaller" than the start time, so a simple subtraction goes negative. Count the hours to midnight and add the hours after: 22:00 to 06:00 = 2 + 6 = 8 hours. The time calculator detects overnight shifts automatically.
How to calculate hours worked in Excel or Google Sheets
Put the start time in A2, end time in B2 and unpaid break (as a time, such as 0:30) in C2. Format A2:C2 as Time.
| What you want | Formula |
|---|---|
| Hours worked (decimal) | =(B2-A2)*24 |
| Hours worked minus lunch | =(B2-A2-C2)*24 |
| Overnight shifts | =MOD(B2-A2,1)*24 |
| Hours as h:mm instead | =B2-A2-C2, formatted as [h]:mm |
| Pay | =(B2-A2-C2)*24*D2, with the hourly rate in D2 |
Excel stores times as fractions of a day, which is why you multiply by 24 to get hours. Use the [h]:mm format for weekly totals — without the square brackets, totals over 24 hours "wrap around" and show the wrong number.
Monthly and yearly hours
- Per week: add up each day.
- Per month: weekly hours × 52 ÷ 12. A 40-hour week averages about 173.3 hours a month.
- Per year: 40 hours × 52 weeks = 2,080 hours, before holidays and leave.
Overtime basics
In the United States, the Fair Labor Standards Act requires overtime of at least 1.5 times the regular rate for non-exempt employees for hours over 40 in a workweek. Some states, such as California, also have daily overtime rules, and other countries set their own limits. Check your contract and local labour law. Using the example week above, someone working 45 hours at 20 an hour would earn 40 × 20 + 5 × 30 = 950.
Common timesheet mistakes
- Writing minutes as decimals. 7 hours 45 minutes is 7.75 hours, not 7.45.
- Forgetting unpaid breaks — or subtracting paid breaks that should count.
- AM/PM mix-ups. Use 24-hour time to avoid them; the military time converter converts between the two.
- Rounding each day instead of the total. Round once, at the end, to two decimal places — or follow your employer's rounding policy.
Frequently asked questions
How do I calculate hours worked in a day?
Subtract the start time from the end time, then subtract any unpaid breaks. 9:00 AM to 5:30 PM with a 30-minute lunch is 8 hours 30 minutes minus 30 minutes = 8 hours.
How do I convert minutes to decimal hours?
Divide the minutes by 60. 15 minutes = 0.25 hours, 30 minutes = 0.5, 45 minutes = 0.75, and 20 minutes ≈ 0.33.
How do I calculate hours worked overnight?
Count the hours to midnight and add the hours after midnight. 10:00 PM to 6:00 AM is 2 hours + 6 hours = 8 hours. In Excel, use =MOD(end-start,1)*24.
How many hours are in a full-time work year?
40 hours a week × 52 weeks = 2,080 hours, before holidays and leave.
When does overtime start?
It depends on where you work. In the US, the Fair Labor Standards Act requires overtime at 1.5 times the regular rate for non-exempt employees after 40 hours in a workweek. Other countries and some US states have different rules.
ERP developer and AI & automation engineer who builds and maintains every tool on WebToolNest. More about me.