Excel guide · 6 min read
Payroll and timesheet formulas in Excel
=MIN(threshold,hours) and overtime =MAX(0,hours-threshold). Gross pay is regular hours x rate plus overtime hours x rate x the overtime multiplier. Keep the threshold, multiplier and rates in their own cells, because they come from your contract or employer, not from Excel.Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D | E | F | G | H | I | J | K |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Day | In | Out | Break (min) | Paid hours | Employee | Week hours | Rate ($/hr) | OT after (hrs/week) | ||
| 2 | Mon | 8:00 AM | 4:30 PM | 30 | 8 | Ana Lopez | 46 | 24 | 40 | ||
| 3 | Tue | 8:00 AM | 5:15 PM | 30 | 8.75 | Ben Okafor | 38.5 | 21.5 | OT multiplier | ||
| 4 | Wed | 7:45 AM | 4:30 PM | 30 | 8.25 | Cara Singh | 46 | 26 | 1.5 | ||
| 5 | Thu | 8:00 AM | 6:00 PM | 45 | 9.25 | Dev Patel | 40 | 19.75 | Annual salary | ||
| 6 | Fri | 8:00 AM | 4:00 PM | 30 | 7.5 | 52000 | |||||
| 7 | Sat | 10:00 PM | 2:30 AM | 15 | 4.25 | Paid hours/week | |||||
| 8 | 40 |
Download this sheet as an Excel practice file (.xlsx)
Paid hours from clock times (overnight shifts included)
Columns B and C hold real Excel times, so 4:30 PM is stored as a fraction of a day (0.6875). Subtracting gives a fraction too, and *24 turns it into decimal hours you can multiply by a rate. The break in D is in minutes, so it is divided by 60. The background is in how to calculate hours worked and convert time to decimal hours.
MOD(C7-B7,1) is the part that makes overnight shifts work. Saturday runs from 10:00 PM to 2:30 AM, so Out minus In is negative (-0.8125). MOD wraps it back around the clock to 0.1875 of a day, which is 4.5 hours, less the 15-minute break. Using MOD on every row is harmless for day shifts, so one formula fills the whole column. More on this in hours worked past midnight; for shifts longer than 24 hours, enter date and time together and see hours between two dates and times.
ROUND(...,2) keeps each day at two decimals, so the weekly total matches what a person adds up by hand. If your workplace rounds punches to the quarter hour, round the times first: see round time to the nearest 15 minutes.
Result: 4.25
4.25 paid hours for the 10:00 PM to 2:30 AM shift, instead of a negative number.
Weekly hours
Add the paid-hours column. Because E already holds decimal hours, a plain SUM works and there is no [h]:mm formatting to worry about. If you keep times instead of decimals, a total over 24 hours needs the custom format [h]:mm, or it wraps around (see sum time in Excel).
For a timesheet log with many people and many weeks in one list, total each person and week with SUMIFS on a week-start column: hours per week from a timesheet shows the setup. Ana's week below is 46 hours, which goes into H2 of the team summary.
Result: 46
46 hours worked in Ana's week: 8 + 8.75 + 8.25 + 9.25 + 7.5 + 4.25.
Regular and overtime hours against a threshold cell
Overtime hours are whatever is above the threshold in K2, and never below zero: =MAX(0,H2-$K$2). Regular hours are the rest, capped at the threshold: =MIN($K$2,H2). For Ana that is 40 regular and 6 overtime; for Ben's 38.5-hour week it is 38.5 and 0.
The threshold is a cell, not a 40 typed into the formula, because overtime rules differ by country, state, industry and contract. Some count per day, some per week, some both. Type the number that applies to your workplace. This page shows the arithmetic only and says nothing about what anyone is owed. Daily overtime (hours over a daily limit), both rules together without double counting, and double time are worked through in how to calculate overtime in Excel.
Result: 6
6 overtime hours for Ana (46 - 40).
Gross pay for one employee
Regular hours at the base rate plus overtime hours at the rate times the multiplier in K4 (1.5 here). The $ signs keep K2 and K4 fixed while you fill the formula down the team list. ROUND to cents on each person, because that is what a payslip shows.
Rounding matters when you total. If you round each person and add the rounded amounts, the total can differ by a cent from rounding the unrounded total. Pick one method (round per person is the usual one) and use it everywhere, so the register and the bank file agree.
Result: 1176
$1,176.00 for Ana: 40 x $24 + 6 x $36.
The whole team in one cell
You can fill the per-person formula down and SUM it, or get the team total directly. The trick is to think of overtime as a premium: pay every hour at the base rate, then add the extra half (K4-1) for the hours above the threshold. (H2:H5>K2) is TRUE only for people over 40 hours, so everyone else adds no premium.
Check one cell against the other: the per-person formula filled down G2:G5 should add up to the same amount. If it does not, a rate or hours cell is probably text (see numbers stored as text). The team's overtime hours are =SUMPRODUCT((H2:H5>K2)*(H2:H5-K2)), which is 12 here: 6 for Ana and 6 for Cara. Comparing labor cost with sales is covered in labor cost percentage.
Result: 4067.75
$4,067.75 gross pay for the four employees this week: $3,917.75 at base rates plus a $150 overtime premium.
Hourly rate from an annual salary
To compare a salaried person with hourly staff, or to price their overtime, divide the salary by the paid hours in a year: hours per week times 52. With $52,000 and 40 hours a week that is $25.00 an hour. Use the paid hours in your contract, not 40 by habit; a 37.5-hour week gives a higher rate. Variations (2,080 hours, weekly and biweekly pay) are in hourly rate from salary.
Taxes, benefits and other deductions are deliberately not on this page; payroll formulas in Excel shows how to carry deductions as input cells through to net pay. Their rates and rules depend on where you are and change often; take them from your payroll provider or accountant and put them in input cells, never as numbers inside formulas.
Result: 25
$25.00 an hour from a $52,000 salary at 40 paid hours a week.
Templates and related guides
Ready-made sheets that use these formulas: the free timesheet template (weekly hours, overtime and pay), the weekly shift schedule template, the PTO tracker and the freelance hourly rate calculator. Managing a team? Excel for HR and people ops collects the HR formulas in one place.
- Time math: elapsed time and time difference, convert minutes to hours, convert text to time, average time, add hours to a time.
- Attendance: attendance tracker formulas, absenteeism rate.
- When the hours look wrong: negative hours or ##### on overnight shifts mean MOD is missing; a pay figure that is far too small means the *24 is missing; a total that resets after 24 hours needs [h]:mm. If a formula still misbehaves, see why is my Excel formula not working.
Common mistakes
Multiplying a time difference straight by the hourly rate
8 hours stored as a time is 0.333 of a day, so 8 hours at $24 an hour pays $8. Multiply by 24 first, or keep a decimal-hours column like E.
Typing the overtime threshold or multiplier inside the formula
=MAX(0,H2-40) breaks silently when the rule or contract changes. Put 40 and 1.5 in input cells and refer to them with $ signs.
Overnight shifts showing ##### or negative hours
Out is smaller than In after midnight. Use MOD(Out-In,1)*24, or enter full dates and times.
Typing 8.30 for half past eight
8.30 is 8.3 hours (8 hours 18 minutes). Type 8:30 for a real time, or 8.5 in decimal hours.
FAQ
What is the Excel formula for payroll hours?
For one shift, =(Out-In)*24 minus the break in hours; use =MOD(Out-In,1)*24 if a shift can cross midnight. Add the days with SUM, then split regular and overtime with MIN and MAX against a threshold cell.
How do I calculate gross pay with overtime in Excel?
=MIN(threshold,hours)*rate + MAX(0,hours-threshold)*rate*multiplier, with the threshold, rate and multiplier in their own cells. Wrap it in ROUND(...,2) to get cents.
Can Excel calculate payroll taxes and deductions?
Excel can do the arithmetic, but the rates and rules are not built in and they vary by location and change over time. Get them from your payroll provider or an accountant and keep them in input cells you can update.
How do I total timesheet hours over 24 in Excel?
Convert to decimal hours with *24 and SUM them, or keep times and format the total cell with the custom format [h]:mm so it shows 46:00 instead of wrapping to 22:00.
How do I convert an annual salary to an hourly rate in Excel?
Divide the salary by the paid hours per year: =salary/(hours_per_week*52). $52,000 at 40 hours a week is $25.00 an hour.
Functions used
MODRemainder after divisionROUNDRounds to digitsSUMAdds numbersMINSmallest numberMAXLargest numberSUMPRODUCTMultiply then addTIMEBuilds a timeSUMIFSAdds with several conditions
Related guides
- How to calculate overtime in Excel
- Payroll formulas in Excel: a gross-to-net payroll register
- How to calculate hours worked in Excel
- Calculate hours worked overnight in Excel
- Convert time to decimal hours in Excel
- How to calculate MTBF and MTTR in Excel
Updated