Excel guide · 4 min read
How to calculate units per hour (UPH) in Excel
=MOD(D2-C2,1)*24-E2/60 and UPH is =F2/G2. For a team or shift, divide total units by total hours, =SUM(F2:F7)/SUM(G2:G7), instead of averaging each person's UPH.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 |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Worker | Shift | Clock in | Clock out | Break (min) | Units | Hours | UPH | Standard UPH | |
| 2 | Maria | Day | 6:00 AM | 2:30 PM | 30 | 1240 | 8 | 155 | 140 | |
| 3 | Dev | Day | 6:00 AM | 2:30 PM | 30 | 1105 | 8 | 138.125 | ||
| 4 | Lena | Day | 10:00 AM | 2:00 PM | 0 | 700 | 4 | 175 | Units to ship | |
| 5 | Tomas | Night | 10:00 PM | 6:30 AM | 30 | 980 | 8 | 122.5 | 9000 | |
| 6 | Aisha | Night | 10:00 PM | 6:30 AM | 30 | 1150 | 8 | 143.75 | Paid hours per shift | |
| 7 | Sam | Night | 10:00 PM | 2:00 AM | 0 | 380 | 4 | 95 | 7.5 |
Download this sheet as an Excel practice file (.xlsx)
Hours worked from clock-in and clock-out
UPH is only as good as the hours under it, and hours are where the formula usually breaks. Excel stores a time as a fraction of a day, so 14:30 minus 6:00 is 0.354, not 8.5. Multiply by 24 to get decimal hours.
Night shifts cross midnight: Tomas clocks in at 22:00 and out at 6:30, and 6:30 minus 22:00 is negative. MOD(D5-C5,1) wraps the difference into the 0 to 1 range, so it works for day and night shifts alike. Then subtract the break: it's in minutes, so divide by 60 first. The full formula in G2 is =MOD(D2-C2,1)*24-E2/60.
If your time clock exports durations instead of times (8:00 as a time value), convert those with *24 too. For more on the midnight problem, see time difference over midnight.
Result: 8
8: Tomas worked 22:00 to 6:30 with a 30-minute break, so 8 hours. Without MOD, (D5-C5)*24 gives -15.5.
Units per hour for each worker
With hours in G, UPH in H2 is simply =F2/G2. Maria packed 1,240 units in 8 hours: 155 UPH. Lena worked a 4-hour shift and packed 700, so she's at 175, the highest rate on the sheet even though her unit count is the lowest. That's the point of a rate: it compares people who worked different hours.
Two mistakes produce numbers that look like typos. Dividing units by the raw time difference, =F2/(D2-C2), gives 3,501, because you divided by a fraction of a day. And subtracting break minutes from hours without dividing by 60, =(D2-C2)*24-E2, takes 30 hours off an 8.5-hour shift and gives -21.5.
Round only for display. Keep the full value in the cell and set decimal places with the number format, so totals and comparisons use exact numbers.
Result: 175
175: Lena packed 700 units in 4 hours.
Team or shift UPH: total units over total hours
For a team, add up the units and the hours, then divide: =SUM(F2:F7)/SUM(G2:G7) gives 138.9 for the whole sheet (5,555 units in 40 hours). Averaging the six UPH figures gives a different answer, because AVERAGE gives Sam's 4-hour shift the same weight as an 8-hour one.
The gap is clearer on the night shift. Total over total is 125.5 UPH; =AVERAGEIFS(H2:H7,B2:B7,"Night") says 120.4, pulled down by Sam's short shift at 95. The 125.5 is what the shift actually produced per labor hour, and it's the number that matches your units and payroll hours. The same rule applies to any rate: see weighted average.
For a summary by shift, use SUMIFS on top and bottom: the day shift comes to 152.25 UPH.
Result: 125.5
125.5: the night shift packed 2,510 units in 20 hours.
Compare against a standard rate
Put your standard or target rate in its own cell (J2, 140 UPH here) rather than typing it into formulas. Each person's performance is then =H2/$J$2, formatted as a percentage: Maria is at 111% of standard, Tomas at 88%.
To list who is below 90% of standard, FILTER does it in one cell in Excel 365: =FILTER(A2:A7,H2:H7<$J$2*0.9,"None") returns Tomas and Sam. In older versions, add a column with =IF(H2<$J$2*0.9,"Below","") and filter on it.
Treat this as a starting point for a conversation, not a verdict. A worker on a new product line, a jammed conveyor or a short shift with a long setup at the start all show up as a low rate. Note the reason next to the number.
Result: 0.875 shows as 88% with Percent format
0.875: Tomas is at 88% of the 140 UPH standard.
Turn UPH into hours and headcount for a target
UPH also works backwards. If 9,000 units have to ship and the standard is 140 UPH, you need =J5/J2 = 64.3 labor hours. Divide by the paid hours in a shift (7.5 in J7) and round up, because you can't schedule part of a person.
Use the rate you actually achieve, not the one you hope for. The team's real rate here is 138.9, which needs 64.8 hours; still 9 people, but on a bigger order the gap between standard and actual is what turns into overtime.
Result: 9
9: 64.3 hours of work at 140 UPH, in 7.5-hour shifts, needs 9 people.
Common mistakes
Dividing by the time difference instead of hours
A time difference is a fraction of a day. Multiply by 24 first, or UPH comes out around 24 times too high.
Negative hours on night shifts
Wrap the subtraction in MOD(clock out - clock in, 1), which handles shifts that cross midnight.
Averaging individual UPH for the team
Divide total units by total hours, so a 4-hour shift doesn't count as much as an 8-hour one.
Leaving breaks in the hours
Subtract unpaid break time (minutes / 60) so the rate reflects time actually worked.
FAQ
What is the formula for units per hour?
Units per hour = units produced / hours worked. In Excel, =F2/G2 with units in F2 and decimal hours in G2. If you only have clock times, hours are =MOD(D2-C2,1)*24 minus any break.
How do I calculate units per labor hour for a team?
Add all units and all hours, then divide: =SUM(F2:F7)/SUM(G2:G7). Don't average each person's UPH; that over-weights short shifts.
Should breaks count in units per hour?
Most operations measure against time actually worked, so unpaid breaks come out. Whatever you choose, use the same rule for everyone and for the standard rate you compare against.
How do I convert units per hour to units per minute?
Divide by 60. 150 UPH is 2.5 units per minute, and 60 / UPH gives the minutes per unit (0.4 minutes, or 24 seconds).
Functions used
MODRemainder after divisionSUMAdds numbersSUMIFSAdds with several conditionsAVERAGEIFSAverages with several conditionsFILTERKeeps matching rows, spillsROUNDUPRounds away from zeroTIMEBuilds a timeIFPicks a result from a test
Related guides
- How to calculate hours worked in Excel
- Calculate hours worked overnight in Excel
- How to calculate a weighted average in Excel
- Convert time to decimal hours in Excel
- How to make a trial balance in Excel
- How to calculate MTBF and MTTR in Excel
- How to calculate takt time in Excel
Updated