Excel guide · 5 min read
How to calculate takt time in Excel
=B6/B5. In the sample, 860 minutes of available time and demand of 860 units a day give a takt of 60 seconds: the line has to finish one unit every minute to keep up.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 |
|---|---|---|---|---|---|---|---|
| 1 | Input | Value | Station | Cycle time (sec) | Gap to takt | Status | |
| 2 | Shift length (min) | 480 | Kitting | 42 | -18 | OK | |
| 3 | Breaks per shift (min) | 50 | Sub-assembly | 58 | -2 | OK | |
| 4 | Shifts per day | 2 | Main assembly | 71 | 11 | Over takt | |
| 5 | Customer demand per day | 860 | Testing | 55 | -5 | OK | |
| 6 | Available time (sec) | 51600 | Packing | 38 | -22 | OK | |
| 7 | Takt time (sec) | 60 | |||||
| 8 | |||||||
| 9 | Net shift (h:mm) | 7:10 AM |
Download this sheet as an Excel practice file (.xlsx)
Available time first: take the breaks out
Put every input in its own cell: shift length in minutes (B2), breaks and planned stops per shift (B3), shifts per day (B4) and customer demand per day (B5). Available time in seconds is =(B2-B3)*B4*60. Two 8-hour shifts with 50 minutes of breaks each give 860 minutes, or 51,600 seconds.
Only remove planned time: breaks, lunch, scheduled meetings, planned cleaning. Don't subtract breakdowns or changeover overruns. Takt describes the pace demand requires; losses are what you measure against it, for example with OEE.
Result: 51600
51600: available production seconds per day.
The takt time formula
Takt time in B7 is =B6/B5. Keep demand and available time for the same period: per day with per day, or per week with per week. Mixing a weekly demand with a daily available time is the usual reason a takt comes out five times too short.
Pick one unit for the whole sheet. Seconds suit lines where a unit takes under a few minutes; for slower work, leave out the *60 and get minutes per unit. To show 60 seconds as 1:00, use =TEXT(B7/86400,"m:ss"), because Excel time is a fraction of a day and a day has 86,400 seconds.
Result: 60
60: one unit every 60 seconds meets demand of 860 a day.
Times typed as h:mm
If the available time per shift is typed as a time, like 7:10 in B9, Excel stores it as a fraction of a day (about 0.299). Multiply by 1440 for minutes or by 86400 for seconds before dividing: =B9*86400*B4/B5 also gives 60. Dividing the raw time by demand gives a tiny decimal that looks like nonsense, which is the most common takt-time mistake in spreadsheets that track shifts as clock times.
Result: 430
430: 7 hours 10 minutes as minutes, the same as 480 - 50.
Compare each station with takt to find the bottleneck
List each station's cycle time in E (time for one unit at that station). F2 is =E2-$B$7, the gap to takt, and G2 is =IF(E2>$B$7,"Over takt","OK"). The $ keeps every row pointing at the one takt cell.
=XLOOKUP(MAX(E2:E6),E2:E6,D2:D6) names the slowest station: Main assembly at 71 seconds, 11 over takt. A station over takt caps the line: =INT(B6/MAX(E2:E6)) shows the line can make only 726 units a day against demand of 860. In Excel 365, =FILTER(D2:E6,E2:E6>B7) lists every station over takt with its time.
Result: 726
726: daily output at the bottleneck's pace, 134 short of demand.
Minimum number of operators
Total work content is the sum of all cycle times, 264 seconds per unit. Dividing by takt gives 4.4, but you can't staff 0.4 of a person, so round up: =CEILING(SUM(E2:E6)/B7,1) or =ROUNDUP(SUM(E2:E6)/B7,0). Both return 5.
Line balance efficiency, =SUM(E2:E6)/(5*B7), is 88%: five people at a 60-second takt have 300 seconds of capacity for 264 seconds of work. The minimum only holds if the work can actually be split so no station is over takt, which here means moving at least 11 seconds of work off Main assembly.
Result: 5
5: the minimum number of operators for 264 seconds of work at a 60-second takt.
Balance to a planned cycle time, not to takt itself
A line balanced exactly to takt has no slack: every jam, missing part or slow start means missed demand, and the only way to catch up is overtime. That's why many plants balance stations to a planned cycle time a little below takt, typically takt multiplied by the efficiency they actually achieve. Keep that efficiency in its own input cell (85% here) so the assumption is visible.
At 85%, the planned cycle time is =B7*0.85, 51 seconds. Now the station list tells a different story: against takt only Main assembly was over, but against 51 seconds Sub-assembly (58) and Testing (55) need work too. Fixing Main assembly alone would still leave a line that misses demand on any day with a few stops.
Result: spills 3 rows × 2 columns
| Sub-assembly | 58 |
| Main assembly | 71 |
| Testing | 55 |
Spills Sub-assembly 58, Main assembly 71 and Testing 55: the three stations slower than a 51-second planned cycle time.
How much overtime until the bottleneck is fixed
While the slowest station is over takt, you can still meet demand by giving that station more time. Demand times the bottleneck's cycle time is the minutes of work it has to do; subtract the minutes it has in the normal shifts.
Main assembly needs 860 x 71 seconds, about 1,018 minutes a day, but the two shifts give it 860. That's 158 minutes of overtime a day, roughly 80 minutes per shift, for that one station (plus whatever buffer of parts it needs from the stations before it). Put that cost next to the cost of moving 11 seconds of work elsewhere and the business case writes itself.
Result: 158
158: extra minutes per day the bottleneck station needs to make 860 units.
Common mistakes
Subtracting unplanned downtime from available time
Remove only planned breaks and stops; measure breakdowns against takt instead.
Dividing an h:mm time directly by demand
Multiply the time by 1440 (minutes) or 86400 (seconds) first.
Mixing periods
Use daily demand with daily available time, or weekly with weekly.
Rounding operators down
Use CEILING or ROUNDUP; 4.4 people means 5.
FAQ
What is the formula for takt time in Excel?
=available_time/demand, for example =B6/B5 with available seconds per day in B6 and units needed per day in B5.
What is the difference between takt time and cycle time?
Takt time is the pace customer demand requires. Cycle time is how long a station actually takes per unit. A station whose cycle time is above takt is a bottleneck.
How do I convert hours and minutes to seconds in Excel?
Multiply the time by 86400, the number of seconds in a day. =B9*86400 turns 7:10 into 25,800 seconds.
How do I calculate the number of operators needed?
Divide total work content by takt and round up: =CEILING(SUM(E2:E6)/B7,1).
Is takt time the same as lead time?
No. Takt is how often a finished unit must come off the line (every 60 seconds here). Lead time, or throughput time, is how long one unit takes from start to finish, which is at least the sum of the cycle times (264 seconds here) plus any time it waits between stations.
Functions used
CEILINGRounds up to a multipleROUNDUPRounds away from zeroXLOOKUPModern lookup in any directionMAXLargest numberINTRounds down to integerFILTERKeeps matching rows, spillsTEXTNumber to formatted textIFPicks a result from a testSUMAdds numbers
Related guides
- How to calculate OEE in Excel
- Calculate hours worked overnight in Excel
- How to calculate a weighted average in Excel
- How to calculate first pass yield (FPY) in Excel
- How to calculate shipping cost by weight in Excel
- How to calculate economic order quantity (EOQ) in Excel
- How to convert time zones in Excel
Updated