ElyxAI

Routing time gap in Excel: which operation ran over the standard

Methods EngineerRouting Time Gap

A routing time gap is one row per operation on one order. You type the order, the operation, the work center, the standard minutes, and the actual minutes. The gap is actual minus standard when both cells are filled. The flag says over when the gap is above zero, under when the actual is below the standard, and exact when the two numbers match. The first screen is a sheet named Route. Row 1 is the header. Row 2 is one operation you can check by hand: standard 12, actual 15. The gap is 3. The flag is over. There is no money column and no pass or fail on that screen. This sheet is not a budget variance. That sheet compares planned money with money spent. This list compares the two minute numbers you typed on a routing line. It is not a quality inspection, and it is not a driver's time sheet. Marking the row done does not blank the gap. An empty actual does. You type both minute numbers. The question is which operation ran over, and by how many minutes you can add. The extra minutes stay visible after the operation is done, because that is the record. Elyx can write the gap and the flag. You still type the standard and the actual. The trial lasts 7 days, with no card.

The Problem

The routing sheet fails when it lives in a comment. A line says the cut ran a bit long. Nobody can add the extra minutes, because the standard and the actual are not numbers on the same row. A sentence without those two numbers is not a gap. A second failure is mixing this sheet with a budget. A budget row answers which money line is above the plan. A quality row answers which check passed. A logistics row answers how long a driver was out. If you paste money, inspections, or driver hours here, the morning sum no longer means extra minutes on the routing. Keep this sheet to orders, operations, centers, and the two minute numbers. A third failure is clearing the gap when the operation is finished. Done is a status word for the filter. The gap stays, so the 3 minutes on the cut are still there tomorrow. Blank the actual only when the timing was wrong. The sheet still does not invent a standard.

Benefits

Each operation is one row with a standard and an actual you typed.

The gap is actual minus standard when both cells are filled.

Over means the gap is above zero. Under means it is below zero. Exact means the minutes match.

A done row keeps its gap. An empty actual blanks it.

The work center is a filter. It is not in the gap formula.

Step-by-Step Tutorial

1

Create the routing list

Name the sheet Route. In row 1 type: Order, Operation, Center, Standard, Actual, Status, Gap, Flag, Note. Order is the works order. Operation is the step number you use. Center is a word such as CUT. Standard and Actual are minutes you typed. Gap and Flag are formulas. Status is a word from a list. Note is optional. Freeze row 1. Do not merge cells.

One row per operation. An order with a cut and a weld is two rows. Do not pack both minute pairs into one cell.

2

Lock the status words

Select Status from row 2 down. Data, Data Validation, List. The source on a sheet named Lists is open and done. Open means the operation is not finished. Done means the timing on that row is the record you want to keep. Both words keep the gap. The status is only a filter. People must pick a word. A synonym such as closed or finished will not match a later count of open rows. The list does not say the part passed quality.

Standard and Actual are minutes. Do not type the word over in those columns.

3

Enter four operations you can check by hand

On row 2 type order OF-14, operation 10, center CUT, standard 12, actual 15, status open. You will predict gap 3 and flag over. On row 3 type order OF-14, operation 20, center WELD, standard 12, actual 9, status done. Gap should be -3 and flag under. On row 4 type order OF-15, operation 10, center CUT, standard 20, actual 24, status open. Gap should be 4 and flag over. On row 5 type order OF-15, operation 20, center PACK, standard 12, leave Actual blank, status open. Gap and flag should stay blank. Use these minutes before you trust the formulas.

If the operation is not timed yet, leave Actual blank. Do not type zero unless the clock really read zero.

4

Write the gap

The gap is blank when Standard or Actual is blank. Otherwise it is Actual minus Standard. With Standard in D2 and Actual in E2, use =IF(OR(D2="",E2=""),"",E2-D2). Format as a number, not as hours. A positive number is extra minutes. A negative number is minutes under the standard. Zero means the two numbers match. Status is not in this formula. Changing open to done does not change the 3.

=IF(OR(D2="",E2=""),"",E2-D2)

15 minus 12 is 3. 9 minus 12 is -3. 24 minus 20 is 4. If you see another number, the cells are not the minutes you typed.

5

Write the flag

The flag is blank when Standard or Actual is blank. Otherwise it is over when Actual minus Standard is above zero, under when that difference is below zero, and exact when it is zero. Use =IF(OR(D2="",E2=""),"",IF(E2-D2>0,"over",IF(E2-D2<0,"under","exact"))). Over means extra minutes. Under means the actual is below the standard. Exact means a match. The center and the status are not in this formula.

=IF(OR(D2="",E2=""),"",IF(E2-D2>0,"over",IF(E2-D2<0,"under","exact")))

Do not wrap the gap in ABS. Hiding the minus sign hides the weld that came in 3 minutes under 12.

6

Add only the extra minutes

On a sheet named Morning, the over rows are =COUNTIFS(Route!H:H,"over"). With the rows above, that count is 2: the gap of 3 and the gap of 4. The extra minutes are =SUMIFS(Route!G:G,Route!H:H,"over"), which is 7. The under row of -3 is not in that sum. The blank PACK row is not in that count. Open rows are =COUNTIFS(Route!F:F,"open"), which is 3, because done on the weld does not remove it from the gap and the three other rows are still open. Write the rule next to the sum.

=SUMIFS(Route!G:G,Route!H:H,"over")

Do not add the -3 into the extra minutes. Under is a different word, so SUMIFS leaves it out.

7

Keep a finished operation on the sheet

If the flag is over and status is done, the extra minutes are still the record. Do not delete the row to clean the morning list. Filter Flag to over when you want the 3 and the 4. Filter Status to open when you want work that is not finished, including the PACK row whose actual is still blank. A negative gap is not a failed part. The -3 says 9 was typed against a standard of 12. If a done row shows a blank gap while both minute cells are filled, the formula was overwritten.

Filter Center to CUT when you only want that word. The flag formula does not read the center.

8

Check the four rows before you share the sum

Standard 12 and actual 15: gap 3, flag over. Standard 12 and actual 9: gap -3, flag under, and the row can be done without losing the -3. Standard 20 and actual 24: gap 4, flag over. Standard 12 and actual blank: gap blank, flag blank. The over sum is 7, and the over count is 2. If a check fails, fix the minutes before you send the sum. The sum is only this sheet. It is not a budget variance, and it is not a quality result.

Write the four checks on Morning if more than one person times the operations.

Template Features

Gap from the two minute cells

Actual minus standard. Blank when either cell is empty. Status does not blank it.

=IF(OR(D2="",E2=""),"",E2-D2)

Flag from that gap

Over when the gap is above zero, under when it is below zero, exact when it is zero.

=IF(OR(D2="",E2=""),"",IF(E2-D2>0,"over",IF(E2-D2<0,"under","exact")))

Over count

COUNTIFS reads the flag. With the sample rows the count is 2.

=COUNTIFS(Route!H:H,"over")

Extra minutes

SUMIFS adds only gaps flagged over. The sample sum is 7.

=SUMIFS(Route!G:G,Route!H:H,"over")

Done keeps the record

A done row still shows its gap. The open count is a separate filter on status.

=COUNTIFS(Route!F:F,"open")

Concrete Examples

Three extra minutes on the cut

Operation 10 on OF-14 has a standard of 12 and an actual of 15. You want those extra minutes in the sum.

Standard: 12. Actual: 15. Status: open. Center: CUT.

Result: Gap is 3. Flag is over. The over count includes the row. The 12 and the 15 are the minutes you typed.

A second over row makes 7

Operation 10 on OF-15 has a standard of 20 and an actual of 24. The cut is still over by 3.

Standard 20, actual 24, status open. The cut is still 12 and 15.

Result: Gap is 4, flag over. The extra-minute sum is 7, which is 3 plus 4. The over count is 2.

A weld under the standard

Operation 20 on OF-14 has a standard of 12 and an actual of 9. You do not want those minutes in the extra sum, and you want them after the row is done.

Standard: 12. Actual: 9. Status: done.

Result: Gap is -3. Flag is under. The extra sum stays 7. The -3 is still on the sheet because done does not blank the gap.

Pro Tips

Zero actual is a number

Type 0 only when the clock read zero. A blank cell means the operation is not timed, and the gap stays blank. 12 minus 0 would be a gap of -12 and a flag of under.

Center is a label

CUT and WELD let you filter. The flag formula never reads the center. Adding a center does not change the 7.

Text minutes do not subtract

If Standard was pasted as text, E2-D2 will not be 3. Re-enter 12 and 15 as numbers. Then read the flag again.

Distinct from a budget variance

Planned money against spent money belongs on the controller's variance sheet. Pass or fail belongs on the quality list. Driver hours belong on the logistics time sheet. Do not paste those rows here and expect this sum to mean extra routing minutes.

Formulas Used

Don’t want to do it by hand? Ask Elyx

Example request

“Build this in my workbook: Routing time gap, with the formulas, totals and formatting”

Elyx does it directly in your Excel file.

7 days free · No credit card required

Related guides

Frequently Asked Questions