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 hourly rate. 2. The core Excel formula If Excel stores your start and end times correctly, the basic formula is: =(End - Start) * 24 Why multiply by 24? Because Excel represents time as a fraction of a day: 06:00 = 0.25 days 12:00 = 0.50 days 18:00 = 0.75 days Multiplying by 24 converts that fraction into hours. Example A B C Start End Hours 09:00 17:30 8.00 In C2 : =(B2-A2)*24 Result: 8.00 Make sure the result cell is formatted as a number, not as time. 3. Subtract an unpaid lunch break If the shift has an unpaid lunch, subtract it before multiplying by 24. Suppose: Start: 09:00 End: 17:30 Unpaid lunch: 30 minutes If lunch minutes are stored in D2 : =((B2-A2)*24) - (D2/60) Or if lunch is stored as 0:30 : =((B2-A2)-D2)*24 For this example: 17:30 - 09:00 = 8:00 8:00 - 0:30 = 7:30 7:30 = 7.50 decimal hours So the payroll value is: 7.50 Not 7.30 . 4. Why 7.30 is wrong This is the mistake that causes the most confusion. If you worked 7 hours 30 minutes, the decimal version is not 7.30 . It is: 7 + (30 ÷ 60) = 7 + 0.50 = 7.50 decimal ho