如何计算在 Excel 中工作的时间而不中断工资数学

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 to is not on a timesheet. It is hours if you subtract a 30-minute lunch, and payroll usually wants that written as , not . 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: , , Duration answers “how long did it last?” Examples: , , A timesheet usually starts with clock times, but payroll needs durations. That means you have to convert: into: 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: Why multiply by 24? Because Excel represents time as a fraction of a day: Multiplying by 24 converts that fraction into hours. Example A B C Start End Hours 09:00 17:30 8.00 In : Result: 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: End: Unpaid lunch: minutes If lunch minutes are stored in : Or if lunch is stored as : For this example: So the payroll value is: Not . 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 . It is: The decimal part represents a fraction of an hour, not the minute number. Another example: So: Not . 5. Handle shifts that cross midnight A normal subtraction breaks when a shift goes overnight. Example: If you use: Excel will return a negative number. The fix is to add one day when the end time is earlier than the start time: Or more simply: For to , the result is: If you also subtract a 30-minute unpaid lunch: 6. Convert HH:MM into decimal hours manually If you have hours in one column and minutes in another: Hours Minutes Decimal hours 7 45 7.75 Formula: This is the safest formula when your data already separates hours and minutes. Examples: Payroll systems commonly round to two decimal places, so may become . 7. Calculate a weekly total Once every day is in decimal hours, the weekly total is just a sum: Example: Day Decimal hours Mon 8.00 Tue 7.50 Wed 8.25 Thu 7.75 Fri 8.00 Total 39.50 Do not sum clock times like , , and and then treat the result as payroll hours. Convert first, then sum. 8. Split regular and overtime hours If overtime starts after 40 hours in a workweek: Example: If your hourly rate is in and overtime is paid at 1.5×: At $20.00/hour: 9. Avoid the four most common mistakes Mistake 1: Treating 4.35 as 4 hours 35 minutes It is not. So is 4 hours 21 minutes. If you worked 4 hours 35 minutes, the payroll value is: Mistake 2: Subtracting paid breaks Only subtract unpaid lunch or unpaid breaks if your employer does not count them as hours worked. If a break is paid, leave it inside the duration. Mistake 3: Mixing clock time and duration A clock time like is not the same as . is a time of day. is a duration. Convert durations to decimal hours before payroll calculations. Mistake 4: Rounding before checking the rule Some employers round clock times to the nearest: 5 minutes 6 minutes 10 minutes 15 minutes 1/10 hour 1/100 hour The rounding rule matters because two different rules can produce different totals. If you are auditing a paycheck, apply the same rule your employer uses before comparing your result. 10. A compact Excel template A simple timesheet layout can look like this: Day Start End Unpaid lunch Decimal hours Mon 09:00 17:30 30 7.50 Tue 08:30 17:00 30 8.00 Wed 09:15 17:45 30 8.00 Thu 08:00 16:30 30 8.00 Fri 09:00 15:45 30 6.25 For a shift that may cross midnight: For a normal shift: A safer universal formula is: Then sum the decimal hours column. Free calculator If you want to check your Excel formulas without rebuilding the whole sheet, I built a free Weekly Timesheet Calculator that handles: Clock-in and clock-out times Unpaid lunch breaks Overnight shifts Weekly totals Regular and overtime hour splitting Estimated gross pay It runs entirely in the browser and does not require an account: Weekly Timesheet Calculator There is also a full minutes-to-decimal chart here: Minutes to Decimal Conversion Chart Summary The core idea is simple: For clock times in Excel: For overnight shifts: For unpaid lunch: Convert first, sum second, and split overtime only after you know the weekly total. That small order of operations will save you a lot of payroll headaches.