How to Calculate Hours Worked in Excel Without Breaking Payroll Math

If you have ever built a timesheet, you have probably run into the same problem twice: clock times are easy for humans to read, but payroll systems want durations as decimal hours. A shift from 09:00 to 17:30 is not 9.5 on a timesheet. It is 8.00 hours if you subtract a 30-minute lunch, and payroll usually wants that written as 8.00, not 8:00. In this article, we’ll walk through the Excel formulas, the edge cases, and the small time-math mistakes that cause real payroll problems. 1. Clock time and duration are not the same thing Before touching Excel, separate two ideas: Clock time answers “when did this happen?” Examples: 09:00, 17:30, 22:00 Duration answers “how long did it last?” Examples: 8 hours, 7.5 hours, 8.25 hours A timesheet usually starts with clock times, but payroll needs durations. That means you have to convert: 09:00 → 17:30 into: 8.00 decimal hours Once the duration is a decimal number, payroll can multiply it by an h