Why payable hours are where payroll goes right or wrong
If you run payroll for hourly staff, you know that the hardest part is often not the tax or the deductions. It is the hours. The payroll software calculates the rest correctly as long as the hours you feed it are correct. And hours start life in an awkward form: a time clock, an app or a paper sheet records that someone came in at 5:58 and left at 14:07, with a 30-minute break. Payroll needs a number, such as 7.75 hours, that can be multiplied by a rate.
Getting from one to the other involves three small steps that each hide a classic mistake: turning times into hours, taking the break off, and rounding according to company policy. Each mistake is small, a few minutes here or there, but it repeats for every person and every shift. Over a month and a whole workforce, that adds up to real money, and to employees who notice when their pay does not match the hours they believe they worked.
This free first lesson is for payroll administrators, payroll assistants, HR and office staff who prepare hours for a payroll provider, and small business owners who still do this themselves. In the lab above you are the payroll administrator at Larkfield Bakeries, a fictional regional bakery chain. You take one week of clock times for Mia, a baker, turn them into hours worked, round them to the quarter hour, add up the week and calculate her gross pay at an example rate of 16.80 per hour. All rates and rules in the course are example values kept in cells, because real payroll rules differ by country, region and contract.
The core idea: Excel stores time as a fraction of a day
The single most important fact about time in Excel is this: a time is stored as a fraction of one day. Midnight is 0, noon is 0.5, 6:00 is 0.25 and 18:00 is 0.75. When a cell shows 6:02, Excel is really holding about 0.2514, and the time format is only a way of displaying that number.
This makes calculating with times easy, as long as you remember what the result means. If B2 holds 6:02 and C2 holds 14:31, then =C2-B2 returns 0.3535, which is the fraction of a day between the two times. Displayed as a time it reads 8:29, eight hours and 29 minutes. But payroll cannot multiply 8:29 by an hourly rate. It needs 8.48 hours as a decimal number. Because a day has 24 hours, you get there by multiplying: =(C2-B2)*24.
Breaks are usually recorded differently, as a number of minutes such as 30 or 45. Those are ordinary numbers, not times, so 30 means thirty, not half a day. To subtract a break from hours you convert it to hours first by dividing by 60. The complete formula for hours worked is =(C2-B2)*24-D2/60. For Monday that is 8.48 − 0.50 = 7.98 hours.
The last step is rounding. Many employers pay in quarter hours rather than exact minutes. Excel has no “round to the quarter” button, but there is a simple trick: multiply by 4 to count quarters, round that to a whole number, and divide by 4 again. =ROUND(E2*4,0)/4 turns 7.98 into 8.00 and 7.65 into 7.75. Whether you round to the nearest quarter, always down or always up is decided by company policy, the employment contract and the rules that apply where you work, so the formula has to follow the rule, not the other way round.
Worked example: Mia’s week at Larkfield Bakeries
Here is the timesheet from the lab with every formula and its result. Mia starts around 6 in the morning, as bakers do, and her breaks vary from 15 to 45 minutes. The hourly rate of 16.80 sits in its own cell, B9, as an example value.
| Day (A) | In (B) | Out (C) | Break min (D) | Hours worked formula (E) | Result | Payable formula (F) | Result |
|---|---|---|---|---|---|---|---|
| Mon | 6:02 | 14:31 | 30 | =(C2-B2)*24-D2/60 | 7.98 | =ROUND(E2*4,0)/4 | 8.00 |
| Tue | 5:58 | 14:07 | 30 | =(C3-B3)*24-D3/60 | 7.65 | =ROUND(E3*4,0)/4 | 7.75 |
| Wed | 6:05 | 12:20 | 15 | =(C4-B4)*24-D4/60 | 6.00 | =ROUND(E4*4,0)/4 | 6.00 |
| Thu | 6:00 | 14:44 | 45 | =(C5-B5)*24-D5/60 | 7.98 | =ROUND(E5*4,0)/4 | 8.00 |
| Fri | 5:55 | 14:02 | 30 | =(C6-B6)*24-D6/60 | 7.62 | =ROUND(E6*4,0)/4 | 7.50 |
| Week | 37.23 | =SUM(F2:F6) | 37.25 |
Gross pay for the week is =F7*B9, which gives 37.25 × 16.80 = 625.80. Notice that the exact hours worked add up to 37.23, while the payable hours add up to 37.25. Rounding moved some days up and some down, and over this particular week the differences almost cancel out. That is typical for rounding to the nearest quarter. Rounding always down or always up would move every day in the same direction, which is why the choice of rule matters and should be applied consistently to everyone.
Look closely at Tuesday and Friday. Tuesday’s 7.65 hours round up to 7.75, Friday’s 7.62 hours round down to 7.50, even though the two are only two minutes apart. The halfway point between the quarters 7.50 and 7.75 is 7.625, and the two days fall on opposite sides of it. Wednesday needs no rounding at all: 6:05 to 12:20 minus a 15-minute break is exactly 6.00 hours.
The same hours can be rounded in different ways, and each way gives a different week total. This table shows the effect on the two closest days. The functions are all standard Excel. Which one is right for your payroll is a policy and legal question, not an Excel question.
| Policy (example) | Formula | Tue 7.65 h | Fri 7.62 h |
|---|---|---|---|
| Nearest quarter hour | =ROUND(E2*4,0)/4 or =MROUND(E2,0.25) | 7.75 | 7.50 |
| Always down to the quarter | =FLOOR.MATH(E2,0.25) | 7.50 | 7.50 |
| Always up to the quarter | =CEILING.MATH(E2,0.25) | 7.75 | 7.75 |
| No rounding (exact minutes) | =E2 | 7.65 | 7.62 |
How to do it in Excel, step by step
These steps follow the lab. In your own file the clock times usually come from a time-clock export, but the formulas are the same.
- Make sure the clock times are real times, not text. Click B2 and look at the formula bar. If you type times yourself, use a colon, for example
6:02. In the lab they are built with=TIME(6,2,0). - Click E2 and type
=(C2-B2)*24-D2/60, then press Enter. E2 shows 7.98 (or 7.983333 before formatting). - If E2 shows something like 23:35 instead of 7.98, the cell has a time format. Select E2:F7 and press Ctrl+Shift+1, or use Home > Number Format > Number.
- Select E2:E6 and press Ctrl+D to fill the formula down.
- Click F2 and type
=ROUND(E2*4,0)/4. Fill it down to F6. F2 shows 8.00, F3 7.75. - Click F7 and type
=SUM(F2:F6), or press Alt+= on Windows. The result is 37.25. - Click B10 and type
=F7*B9. Format it as currency with Home > Number Format > Currency. The result is 625.80. - Check the result in the other direction: 37.25 hours is 37 hours and 15 minutes, and 37 × 16.80 + 0.25 × 16.80 = 621.60 + 4.20 = 625.80.
=C2-B2 is negative and Excel shows ######. Use =MOD(C2-B2,1)*24 instead. MOD adds a full day when the result is negative, so the formula works for day shifts and night shifts alike.A second useful habit is to keep a check column. Next to the payable hours, =F2-E2 shows how much rounding changed each day. A value larger than 0.125 (half a quarter) in either direction means something is wrong, because nearest-quarter rounding can never move a value further than that. Conditional formatting with Home > Conditional Formatting > Highlight Cells Rules > Greater Than can colour those cells automatically.

Building a timesheet template others can fill in
Once the formulas work for one person and one week, the natural next step is a template that branch managers or team leads can fill in themselves. A good template separates three kinds of cells. Input cells hold what people type: clock-in, clock-out and break minutes. Settings cells hold values that only payroll changes, such as the hourly rate or the rounding step. Formula cells hold everything Excel calculates. Give each kind its own colour, so it is obvious where to type and where not to.
Then protect the parts that should not change. Select the input cells, open Home > Format > Format Cells > Protection (or Ctrl+1) and untick Locked. Then switch on Review > Protect Sheet. Now people can type times and breaks, but they cannot overwrite a formula by accident. Sheet protection in Excel is a guard against mistakes, not a security feature, so keep the master copy of the template in a place only payroll can edit.
Data validation stops many errors before they happen. Select the clock-time cells, choose Data > Data Validation, set Allow to Time and a sensible range, for example between 4:00 and 23:59 for a bakery. For the break column choose Whole number between 0 and 120. Anyone who types 630 instead of 6:30, or a break of 300 minutes, sees an error message straight away instead of producing a strange number at the end of the month.
Finally, keep the rounding step itself in a settings cell instead of hard-coding 4 into the formula. With the step of 0.25 in a cell such as H1, the formula becomes =MROUND(E2,$H$1). If the policy ever changes to tenths of an hour or to exact minutes, you change one cell, and the whole template follows. This is the same principle as the hourly rate in B9: every assumption that might change lives in one labelled cell.
Common mistakes and how to spot them
Timesheet errors are rarely dramatic. They are small, systematic and easy to miss, which is exactly why they are worth checking for.
- Forgetting the ×24.
=C2-B2without ×24 gives a fraction of a day such as 0.35. Multiply that by an hourly rate and pay is a tiny fraction of what it should be. If a daily figure is below 1 for a full shift, the ×24 is missing. - Subtracting break minutes as hours.
=(C2-B2)*24-D2subtracts 30 hours instead of 30 minutes and gives a large negative number. Always divide minutes by 60. - Hours displayed as times. When a decimal like 8.5 sits in a cell with a time format, it shows as 12:00 because 0.5 of a day is noon. Format hour columns as numbers.
- Times stored as text. Exports sometimes give “06:02” as text. The subtraction then returns
#VALUE!.=TIMEVALUE(B2)converts text to a real time, or use Data > Text to Columns > Finish on the column. - Rounding the week instead of each day. If your policy rounds each shift, round each day and then add them. Rounding the week total gives a different result, here 37.23 would round to 37.25, but on other weeks the two methods differ.
- Typing the rate into the formula.
=F7*16.8works until the rate changes, and then every copy of the formula is out of date. Keep the rate in its own labelled cell and refer to it. - Night shifts without MOD. A shift that crosses midnight gives a negative result. Use
=MOD(C2-B2,1)*24.
Quick reference: functions and shortcuts from this lesson
| Function or shortcut | What it does on a timesheet | Example |
|---|---|---|
(out-in)*24 | Turns the time difference (a fraction of a day) into hours | =(C2-B2)*24 |
/60 | Turns break minutes into hours | =D2/60 |
ROUND(x*4,0)/4 | Rounds hours to the nearest quarter | =ROUND(E2*4,0)/4 |
MROUND | Rounds to the nearest multiple | =MROUND(E2,0.25) |
FLOOR.MATH / CEILING.MATH | Rounds down or up to a multiple | =FLOOR.MATH(E2,0.25) |
MOD(out-in,1) | Handles shifts that end after midnight | =MOD(C2-B2,1)*24 |
TIME | Builds a time from hours and minutes | =TIME(6,2,0) |
SUM | Adds the payable hours of the week | =SUM(F2:F6) |
Format [h]:mm | Shows time totals above 24 hours correctly | Ctrl+1 > Custom |
| Ctrl+Shift+1 | Number format with two decimals, so hours show as 7.75 | Select E2:F7 |
| Ctrl+Shift+; | Enters the current time in a cell (Windows) | Quick test entries |
| Ctrl+D | Fills the formula down | Select E2:E6, press Ctrl+D |
Real-world uses
The same three steps, convert, subtract, round, are behind many everyday payroll tasks.
- Checking a time-clock export before it goes to the payroll provider. Rebuild the payable hours in Excel for a sample of employees and compare them with what the system calculated.
- Shops and sites without a time clock. Small branches often send paper or spreadsheet timesheets. A template with these formulas turns them into payable hours in minutes.
- Answering an employee query. When someone says their pay is short, you can show day by day how their clock times became payable hours and where rounding moved them.
- Comparing rounding policies. Before a policy change, calculate a month of hours with nearest, down and up rounding side by side and see the effect on cost and on each employee.
- Preparing overtime and premiums. Payable hours per day are the input for overtime and night-shift calculations, which is exactly where the next lesson continues.
What you will build in the rest of this course
Lesson 1 is free. The other nineteen lessons are part of the paid course, a one-time purchase of $9.90. Each is a hands-on lab with the same Coach that checks your formulas as you go, and together they take Larkfield Bakeries’ payroll from hours to a monthly summary.
Overtime and Night-Shift Premiums. You split each baker’s weekly hours into regular and overtime hours and add overtime and night premiums to gross pay, with all thresholds and percentages as example inputs.
Salaried vs Hourly Staff: One Gross-Pay Formula for Both. You calculate the October gross pay of salaried and hourly employees with one formula and split the payroll by pay type.
Pre-Tax Deductions: Pension Contributions and Taxable Pay. You calculate each employee’s pension contribution on the pensionable part of the salary and the taxable pay after pre-tax deductions.
Withholding from a Rate Table: Approximate-Match Lookups. You look up each employee’s withholding rate in an example rate table and learn why approximate-match lookups are the right tool for bands.
Gross-to-Net per Employee: From Gross Pay to the Amount Paid Out. You build the full gross-to-net calculation for six employees: pension, social contributions, tax and after-tax deductions.
The Payroll Register: Totals, Cross-Foot Check and Department Totals. You check a register exported from payroll software: every row must cross-foot, the totals must agree, and you summarise gross pay by department.
Employer Cost on Top of Gross Pay. You add the employer’s own contributions to each employee’s gross pay and see what one unit of gross pay really costs the bakery.
Year-to-Date Totals Across Months. You build year-to-date gross pay per employee and a cumulative company total that grows month by month.
New Starters and Leavers: Pro-Rata Pay by Working Days. You calculate the November pay of two leavers, one new starter and one full-month employee, pro rata by working days with NETWORKDAYS.
Payroll Checks: Variance vs Last Period and Flags. Before releasing a pay run you compare every net pay with the previous month, flag big changes and highlight them.
Payroll Summary Dashboard: Department Costs and a Chart. You prepare the monthly payroll summary for the managing director with cost per department, shares, cost per head and a chart.
Summary
Excel stores times as fractions of a day, so payable hours start with =(C2-B2)*24. Subtract break minutes as D2/60, round with =ROUND(E2*4,0)/4 if your policy pays in quarter hours, add the week with =SUM(F2:F6) and multiply by the rate kept in its own cell. For Mia’s week at Larkfield Bakeries that gives 37.25 payable hours and a gross pay of 625.80. Scroll back up and try the free lab now: fill E2:F7 and B10, and let the Coach confirm each step.
Frequently asked questions
How do I calculate hours worked between two times in Excel?
Subtract the start time from the end time and multiply by 24, for example =(C2-B2)*24. Excel stores times as fractions of a day, so the ×24 turns the result into decimal hours.
How do I subtract an unpaid break in minutes?
Divide the minutes by 60 before subtracting: =(C2-B2)*24-D2/60. A 30-minute break then reduces the hours by 0.5.
How do I round hours to the nearest quarter hour in Excel?
Use =ROUND(E2*4,0)/4 or =MROUND(E2,0.25). To always round down or up, use FLOOR.MATH or CEILING.MATH with 0.25. Which rule applies is a matter of company policy and local rules.
Why does my hours total show 12:00 instead of 8.5?
The cell has a time format, and 0.5 of a day is displayed as 12:00. Change the format to Number with Ctrl+Shift+1 or Home > Number Format > Number.
How do I calculate hours for a shift that goes past midnight?
Use =MOD(C2-B2,1)*24. MOD adds a full day when the end time is earlier than the start time, so night shifts give the correct positive number of hours.
How do I calculate weekly gross pay from hours?
Add the payable hours with SUM and multiply by the hourly rate kept in its own cell, for example =F7*B9. Keeping the rate in a cell means you only change it in one place.
Keep going: Excel for Payroll Administrators: Hours, Gross-to-Net, Registers & Checks
This free lab is lesson 1 of 20. Unlock the other 19 hands-on labs, the final exam and your certificate for a one-time $9.90.
- Overtime and Night-Shift Premiums
- Salaried vs Hourly Staff: One Gross-Pay Formula for Both
- Pre-Tax Deductions: Pension Contributions and Taxable Pay
- Withholding from a Rate Table: Approximate-Match Lookups
- Gross-to-Net per Employee: From Gross Pay to the Amount Paid Out
- The Payroll Register: Totals, Cross-Foot Check and Department Totals
- Employer Cost on Top of Gross Pay
- Year-to-Date Totals Across Months
- New Starters and Leavers: Pro-Rata Pay by Working Days
- Payroll Checks: Variance vs Last Period and Flags
- Payroll Summary Dashboard: Department Costs and a Chart
- Holiday Accrual and Balances: Months Employed, Days Accrued, a Carry-Over Cap and the Holiday Liability
- Unpaid Leave and Sick Pay: Daily Rate, Deductions, the Sick-Pay Reduction and the Adjusted Gross
- Contribution Ceilings: Year-to-Date Pay, the Part Still Subject to Contributions and the Month the Cap Is Reached
- Back Pay and Pro-Rata Bonus: New Rates Rounded to the Cent, Back Pay for Past Hours and a Bonus by Months Employed
- The Payroll Calendar: Pay Dates on the Last Working Day, Timesheet Cut-Offs, Bank File Deadlines and Holidays
- Timesheet Exceptions: Missing Clock-Outs, Over-Long Shifts and Short Breaks Before the Pay Run
- Net Pay to Bank File: Matching Payments by Employee ID, Differences, Duplicates and Unknown Payees
- Final Project: Year-End Payroll Dashboard – Total Cost per Month, Cost per Head, Overtime Share, Big Changes and a Chart