How to calculate hours worked
- Enter start and end times. For each day worked, enter the clock-in and clock-out time. Night shifts past midnight are handled automatically.
- Enter unpaid breaks. Add the unpaid break minutes for each day, such as a 30-minute lunch.
- Read the daily and weekly totals. Each day shows hours worked in hours and minutes and in decimal. The weekly total appears below.
- Add a rate for pay. Optionally enter an hourly rate and overtime threshold to see regular, overtime and total pay.
Example: 8:45 AM to 5:15 PM is 8 hours 30 minutes. Take off a 45-minute unpaid lunch and that day is 7 hours 45 minutes, or 7.75 hours. Five days like that make a 38.75-hour week.
Converting minutes to decimal hours
Payroll and invoices use decimal hours, because 7.75 hours × $20 is easy to multiply and 7:45 isn’t. Divide the minutes by 60:
| Minutes | Decimal hours | Minutes | Decimal hours |
|---|---|---|---|
| 5 min | 0.08 | 35 min | 0.58 |
| 10 min | 0.17 | 40 min | 0.67 |
| 15 min | 0.25 | 45 min | 0.75 |
| 20 min | 0.33 | 50 min | 0.83 |
| 25 min | 0.42 | 55 min | 0.92 |
| 30 min | 0.50 | 60 min | 1.00 |
Night shifts that go past midnight
When a shift ends the next day, the end time is smaller than the start time, so a plain subtraction goes negative. Add 24 hours to the end time first: 10:00 PM to 6:30 AM is (6.5 + 24) − 22 = 8.5 hours. The calculator above spots this automatically and marks the day as overnight. Count the whole shift on the day it started, so a Friday night shift stays on Friday.
Should breaks count as hours worked?
Unpaid meal breaks (usually 30 to 60 minutes) are taken off. Short paid rest breaks, often 5 to 20 minutes, normally count as working time. Rules differ by country and contract, so enter only the unpaid minutes in the Break column. If someone works through lunch, enter 0.
Hours worked formula in Excel and Google Sheets
With the start time in B2, end time in C2 and unpaid break minutes in D2:
MOD(…, 1) is what makes overnight shifts work, since Excel stores times as fractions of a day. Format the decimal version as a number and the second as [h]:mm so totals over 24 hours display correctly. Sum the column for the weekly total.
From hours worked to pay
Multiply decimal hours by the hourly rate. Hours over your overtime threshold (for example 40 a week) are paid at the overtime rate, usually 1.5×. The timesheet above does this when you enter a rate; for monthly salaries, double time or country rules, see the overtime calculator.
Filling in timesheets by hand every week is slow and easy to fudge. With Clocked Live your team checks in and out from their phone, breaks are timed automatically, night shifts are handled, and worked and overtime hours for every employee are ready for payroll at month end.