How to calculate hours worked: the formula, overnight shifts and the decimal mistake that costs money

Working out how long someone worked sounds like something you learned at school. Then Friday comes, you have a stack of start and finish times, a few lunches that ran long, one shift that ended at 2 a.m., and a payroll deadline. That’s when small mistakes creep in, and the most common one isn’t in the subtraction. It’s in what people do with the minutes afterwards.

Here is the method we’d use by hand, the table that saves you from the decimal trap, and spreadsheet formulas you can copy.

The formula, step by step

1. Write both times on a 24-hour clock. 8:50 a.m. stays 8:50. For p.m. times, add 12 to the hour: 5:15 p.m. becomes 17:15. This removes the a.m./p.m. confusion from the calculation.
2. Subtract the start from the finish. 17:15 minus 8:50. If the finish minutes are smaller than the start minutes, borrow an hour: 17:15 is the same as 16:75, and 16:75 minus 8:50 is 8 hours 25 minutes.
3. Take off unpaid breaks. 8:25 minus a 30-minute unpaid lunch leaves 7 hours 55 minutes. Only take off breaks that are genuinely unpaid; more on that below.
4. Convert the minutes to a decimal. Divide the minutes by 60: 55 ÷ 60 = 0.92. So 7 hours 55 minutes is 7.92 hours.
5. Multiply by the hourly rate, or add the day to the weekly total if overtime might apply.

Minutes to decimal hours: the table

This is where most hand-calculated timesheets go wrong. Hours and minutes are not a decimal number. Seven hours and 55 minutes written as “7.55” looks right and is wrong by more than 20 minutes.

Minutes	Decimal hours	Minutes	Decimal hours
5	0.08	35	0.58
6	0.10	40	0.67
10	0.17	45	0.75
15	0.25	50	0.83
20	0.33	55	0.92
25	0.42	60	1.00
30	0.50

Rounded to two decimals. For any other number of minutes, divide by 60.

Shifts that cross midnight

A shift from 10:00 p.m. to 6:30 a.m. breaks the simple subtraction, because 6:30 minus 22:00 gives a negative number. The fix is to add 24 hours to the finish time:

If you run night shifts in the US, it also helps to look at when your workweek starts. The FLSA lets it begin on any day and at any hour (29 CFR 778.105), so starting it at a time when nobody is on shift saves you from splitting a single night between two weeks.

Which minutes count as hours worked

The arithmetic is only as good as the start and finish times you feed it. Under the federal rules in the US, a few points catch out a lot of employers:

• Short breaks are paid time. Rest breaks of about 5 to 20 minutes count as hours worked (29 CFR 785.18). Don’t subtract them.
• A real meal break isn’t. A bona fide meal period, ordinarily 30 minutes or more, doesn’t count, but only if the employee is completely relieved from duty (29 CFR 785.19). Eating at the desk while answering the phone is work.
• Work you didn’t ask for still counts. “Work not requested but suffered or permitted is work time” (29 CFR 785.11). If someone stays 20 minutes to finish a task and you know it, those 20 minutes go on the timesheet.
• Rounding has to be neutral. Rounding to the nearest 5 minutes, or to the nearest tenth or quarter of an hour, is accepted only if over time it averages out so employees are paid for all the time they actually work (29 CFR 785.48(b)). Paying to the minute avoids the question altogether.

Outside the US the details differ, but the starting point is the same: the record has to show when people actually started and stopped. What each country expects you to keep, and for how long, is in how long to keep timesheets.

Adding up the week and working out overtime

Once each day is in decimal hours, the weekly total is a plain sum. For non-exempt employees in the US, the Fair Labor Standards Act requires at least 1.5 times the regular rate for every hour over 40 in a workweek.

Two details matter here. First, the workweek is “a fixed and regularly recurring period of 168 hours”, seven consecutive 24-hour periods. It doesn’t have to start on Monday, but once you set it, it stays fixed (29 CFR 778.105). Second, each week stands on its own: the Act “does not permit averaging of hours over 2 or more weeks” (29 CFR 778.104). Thirty hours one week and fifty the next means ten hours of overtime, not an average of forty.

Notice that no single day crossed 10 hours, yet the week still owes overtime. Also, the “regular rate” isn’t always the base hourly wage. Bonuses that aren’t excluded by law, such as many attendance or production bonuses, have to be added in when you work out the overtime rate (29 CFR 778.208). Some states, California being the best known, also have daily overtime. The country-by-country picture is in overtime rules in English-speaking countries.

Excel and Google Sheets formulas that work

Spreadsheets store a time as a fraction of a day, so 12:00 is 0.5. That’s why you multiply by 24 to get hours. Lay the sheet out like this, with start and finish entered as times and the unpaid break in minutes:

Column	What goes in it	Example
A	Date	10/26/2026
B	Start time	8:50 AM
C	Finish time	5:15 PM
D	Unpaid break, in minutes	30
E	Hours worked	=ROUND(MOD(C2-B2,1)*24-D2/60,2)

• Hours for the day (E2): =ROUND(MOD(C2-B2,1)*24-D2/60,2) gives 7.92 for the example above. MOD(…,1) is what handles night shifts: for 22:00 to 6:30 it returns 8.5 instead of a negative number. It works for any shift shorter than 24 hours.
• Weekly total: =SUM(E2:E8), with the cell formatted as a number, not a time.
• Overtime hours for the week: =MAX(0,SUM(E2:E8)-40).
• If you’d rather see hours and minutes: =MOD(C2-B2,1)-D2/1440 and format the cell as [h]:mm. The square brackets stop totals above 24 hours from rolling back to zero.

A spreadsheet is fine for adding up. Its weak point is the input: times typed in at the end of the week, cells anyone can overwrite, and no record of what changed. We go through the alternatives in how to track employee hours.

Frequently asked questions

Information current as of 28 October 2026. US states and other countries can add their own rules, so use this as a guide rather than legal advice.

← Back to resources