Cellwise Start free

Excel guide · 4 min read

How to calculate units per hour (UPH) in Excel

Quick answerUnits per hour is units produced divided by hours worked. With clock-in in C2, clock-out in D2 and break minutes in E2, hours are =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.

RowABCDEFGHIJ
1WorkerShiftClock inClock outBreak (min)UnitsHoursUPHStandard UPH
2MariaDay6:00 AM2:30 PM3012408155140
3DevDay6:00 AM2:30 PM3011058138.125
4LenaDay10:00 AM2:00 PM07004175Units to ship
5TomasNight10:00 PM6:30 AM309808122.59000
6AishaNight10:00 PM6:30 AM3011508143.75Paid hours per shift
7SamNight10:00 PM2:00 AM03804957.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.

=MOD(D5-C5,1)*24-E5/60

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.

=F4/G4

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.

=SUMIFS(F2:F7,B2:B7,"Night")/SUMIFS(G2:G7,B2:B7,"Night")

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.

=H5/$J$2

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.

=ROUNDUP(J5/J2/J7,0)

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

Related guides

Updated