Timesheet and time card calculator

How to calculate hours worked

1. Take the start time away from the finish time.
2. Take off any unpaid breaks.
3. Add up the days in minutes.
4. Divide the total minutes by 60 for decimal hours.

Worked example. Monday 08:45 to 17:15 is 8 hours 30 minutes. Take off a 30-minute unpaid lunch and you get 8 hours 0 minutes, or 8.00 decimal hours.

Overnight shifts. If the finish time is earlier than the start time, the shift ran past midnight, so add 24 hours. 22:00 to 06:00 is 8 hours.

Minutes to decimal hours

Divide the minutes by 60. 40 minutes is 40 ÷ 60 = 0.67 hours, so 7 hours 40 minutes is 7.67 hours. Add up in minutes and convert once at the end: three 20-minute entries make exactly 1.00 hour, but adding the rounded decimals (0.33 + 0.33 + 0.33) gives 0.99.

The spreadsheet formula

In Excel or Google Sheets, with the start time in B2, the finish time in C2 and unpaid break minutes in D2, decimal hours are:

=MOD(C2-B2,1)*24-D2/60

MOD(…,1) handles shifts that cross midnight. Format the cell as a number with two decimal places. For hours and minutes instead, use =MOD(C2-B2,1)-D2/1440 and format the cell as [h]:mm.

Rounding

Rounding is off unless you turn it on. In the US, federal rules accept recording start and finish times to the nearest 5 minutes, tenth of an hour or quarter of an hour, but only if it "will not result, over a period of time, in failure to compensate the employees properly for all the time they have actually worked" (29 CFR 785.48). The "7-minute rule" is just quarter-hour rounding: 1 to 7 minutes rounds down and 8 to 14 rounds up. In Australia and the UK we found no general rounding rule; check your award, agreement or contract. When rounding is on, the calculator shows how much it changed the total.

Breaks by country

• Australia: "Awards, enterprise agreements and other registered agreements set the rules for paid and unpaid rest breaks" (Fair Work Ombudsman). Check yours.
• United Kingdom: if you work more than 6 hours a day, you have the right to one uninterrupted 20-minute rest break. It does not have to be paid; that depends on your contract. You also have the right to 11 hours' rest between working days (GOV.UK).
• United States: federal law does not require lunch or coffee breaks. When short breaks of about 5 to 20 minutes are given, they count as work time. Meal periods of typically at least 30 minutes are not work time (US Department of Labor).

Paid breaks are part of the shift, so don't put them in the unpaid breaks box.

How long to keep timesheets

• Australia: employers have to keep time and wages records for 7 years. They must record overtime hours and when overtime started and finished, and the hours of casual and irregular part-time employees paid by the hour. The Fair Work Ombudsman says employers should keep records of hours worked for all employees.
• United Kingdom: employers must keep records that show they keep to the working time limits, such as the 48-hour average week, and keep them for 2 years (Working Time Regulations 1998, regulation 9).
• United States: payroll records for at least 3 years, and time cards and the records wages are worked out from for 2 years. "Employers may use any timekeeping method they choose" (Fact Sheet #21).

Overtime

• United States: the Fair Labor Standards Act requires overtime for hours over 40 in a workweek at not less than one and a half times the regular rate. A workweek is a fixed, regularly recurring 168 hours, and hours cannot be averaged over two or more weeks (Fact Sheet #23). Exempt employees and state rules are not covered here; your state may require more.
• Australia: overtime is work outside the ordinary hours set by your award, agreement or contract, and those set the rates too. Use the Fair Work Ombudsman's Pay Calculator for award rates.
• United Kingdom: "Employers do not have to pay workers for overtime." Your average pay for all the hours you work must not fall below the National Minimum Wage (GOV.UK). The working week is limited to 48 hours on average, normally over 17 weeks, unless you opt out.

Questions