Forum Discussion
Excel calculation of time in step
I’m trying to calculate the amount of time that an item (part) was in each particular Step (highlighted in yellow); beginning with “recdate”, then progressing through each step 01, 03, 05, 08, 09, 10, 13, 15, 1555, 16, 17, 20 and ending with step 18. The problem I’m having is that some of the items (parts), don’t move through every step. How do I calculate the time with the blank cells?
| wono | custno | csr | item | descrip | manuf | dept | workreq | orig_scope | ponum | serno | recdate | woqodate | qoaprvdte | shipdate | awbno | in_house | step_01 | step_03 | step_05 | step_08 | step_09 | step_10 | step_13 | step_15 | step_1555 | step_16 | step_17 | step_20 | step_18 |
| 332225 | 120212 | AB | 8TJ163AAB1 | N1 SPEED SEN | AMETEK | EL3 | SIH | RP | 419514 | 1B74C | 9/1/2020 | 9/8/2020 | 12/9/2021 | 4/1/2022 | SIH | 577 | 09/01/20 14:30 | 09/02/20 06:27 | 09/08/20 11:10 | 09/08/20 13:03 | 09/08/20 13:04 | 12/09/21 15:06 | 04/01/22 08:01 | ||||||
| 338820 | 220699 | EG | QA03558ISS8 | INDICATOR-CL | CFMI | FLP | SIH | RP | 784786-1 | RC12-4539 | 3/4/2021 | 4/4/2021 | 3/28/2022 | 4/1/2022 | SIH | 393 | 03/04/21 14:23 | 03/05/21 06:32 | 03/10/21 10:27 | 03/10/21 12:02 | 04/06/21 21:29 | 03/29/22 08:29 | 04/01/22 08:05 | ||||||
| 344847 | 040535 | EG | 41F1005 | LUBRICATION | SAFRAN | FLP | SIH | TEST | 4701199338 | YT006460 | 7/15/2021 | 1/22/2022 | 11/24/2021 | 4/1/2022 | SIH | 260 | 07/15/21 14:30 | 03/18/22 10:45 | 11/18/21 07:20 | 03/28/22 11:13 | 03/30/22 11:10 | 04/01/22 08:02 | |||||||
| 353215 | 140882 | LR | 55022001-11 | COOLER | SUMITOMO | FCL | OH | RP | 7500264318-10 | 30386 | 1/19/2022 | 3/16/2022 | 4/1/2022 | 4/1/2022 | 534977616551 | 72 | 01/27/22 11:58 | 03/11/22 07:37 | 03/19/22 06:57 | 03/21/22 11:18 | 03/21/22 11:25 | 03/28/22 11:44 | 03/30/22 06:34 | 03/30/22 08:08 | 04/01/22 09:30 | 03/30/22 11:37 | 04/01/22 10:19 | ||
| 353821 | 070237 | EG | 41F1005 | LUBRICATION | SAFRAN | FLP | OH | TEST | W285385 | YT007473 | 1/28/2022 | 2/2/2022 | 2/22/2022 | 4/1/2022 | 534977616562 | 63 | 01/28/22 13:04 | 01/29/22 07:26 | 02/21/22 05:14 | 03/03/22 11:11 | 03/03/22 13:38 | 03/04/22 05:59 | 03/30/22 08:03 | 03/31/22 09:51 | 03/31/22 13:56 | 04/01/22 09:14 | 04/01/22 10:19 | ||
| 355134 | 180049 | AB | 85465-2 | ROTOR | MEGGITT | EL1 | SIH | RP | RS42566601 | R833G | 2/23/2022 | 3/22/2022 | 3/25/2022 | 4/1/2022 | SIH | 37 | 02/23/22 13:52 | 02/25/22 09:19 | 03/17/22 08:46 | 03/17/22 10:42 | 03/17/22 15:12 | 03/29/22 08:21 | 04/01/22 08:05 | ||||||
| 355193 | 211073 | EG | 41F9003 | OIL SCAVENGE | CFMI | FSP | SIH | RP | P2420324-1 | YT118501 | 2/26/2022 | 3/11/2022 | 3/11/2022 | 4/1/2022 | SIH | 34 | 03/21/22 11:24 | 03/21/22 11:59 | 03/11/22 11:17 | 03/14/22 10:17 | 03/14/22 11:15 | 03/14/22 11:28 | 03/18/22 06:55 | 03/24/22 12:38 | 04/05/22 14:08 | 04/05/22 14:08 | |||
| 355216 | 011387 | EG | 45731-1382 | SERVO FUEL H | MEGGITT | FCL | OH | INSP | 1300321 | YB004176-7 | 2/26/2022 | 3/19/2022 | 3/30/2022 | 4/1/2022 | 534977616584 | 34 | 03/07/22 12:45 | 03/18/22 11:47 | 03/14/22 08:40 | 03/30/22 14:27 | 03/31/22 05:48 | 03/31/22 05:57 | 03/31/22 12:59 | 03/31/22 14:39 | 03/31/22 16:10 | 04/01/22 10:20 | |||
| 355363 | 011531 | EG | 3269264-10 | ACTUATOR FEE | HONEYWEL | CDU | SIH | OH | 211121 | GRTC0267 | 3/1/2022 | 3/30/2022 | 3/30/2022 | 4/1/2022 | SIH | 31 | 03/01/22 12:31 | 03/29/22 14:22 | 03/07/22 14:48 | 03/30/22 10:45 | 03/31/22 08:08 | 04/01/22 08:08 | |||||||
| 355436 | 040538 | JJ | 607999-00 | JUNCTION BOX | AMETEK | EL3 | SIH | INSP | 4701260282 | GDBFK09R | 3/2/2022 | 3/16/2022 | - - | 4/1/2022 | SIH | 30 | 03/02/22 13:22 | 03/03/22 11:03 | 03/15/22 15:54 | 03/16/22 06:32 | 03/16/22 10:22 | 03/16/22 12:16 | 04/01/22 08:09 | ||||||
| 355583 | 012041 | AB | 144-186-000-011 | ACCELEROMETE | MEGGITT | EL3 | SIH | RP | 3614765 | MA462209-K | 3/5/2022 | 3/24/2022 | 3/25/2022 | 4/1/2022 | SIH | 27 | 03/05/22 07:25 | 03/07/22 07:38 | 03/23/22 13:12 | 03/23/22 14:22 | 03/24/22 04:42 | 03/29/22 08:24 | 04/01/22 08:03 | ||||||
| 355636 | 210378 | LR | 41F1205 | DMS DETECTOR | CFMI | FLP | SIH | TEST | 0014336238 | YT046904 | 3/7/2022 | 3/16/2022 | 3/31/2022 | 4/1/2022 | SIH | 25 | 03/07/22 11:14 | 03/07/22 15:19 | 03/14/22 13:48 | 03/16/22 09:05 | 03/30/22 15:17 | 04/01/22 08:01 | |||||||
| 355741 | 020333 | EG | 340-400-303-0 | OIL SCAVENGE | CFMI | FSP | OH | TEST | RO485 | YT023204-T | 3/9/2022 | 3/29/2022 | 3/29/2022 | 4/1/2022 | 534977616595 | 23 | 03/09/22 07:10 | 03/09/22 09:29 | 03/24/22 13:32 | 03/29/22 20:40 | 03/30/22 06:02 | 03/30/22 07:30 | 03/31/22 15:09 | 03/31/22 15:10 | 03/31/22 16:07 | 04/01/22 10:20 | |||
| 355747 | 020100 | AB | 16844-000 | FUEL COOLED | AMETEK | FCL | SIH | TEST | RO1027360 | 7171 | 3/9/2022 | 3/22/2022 | 3/24/2022 | 4/1/2022 | SIH | 23 | 03/10/22 15:27 | 03/14/22 13:04 | 03/18/22 09:43 | 03/21/22 06:32 | 03/29/22 08:26 | 04/01/22 08:03 |
2 Replies
- Are the date-times in each of the Step_* columns the date and time at which that step was completed or at which it started?
So, should the difference between step_01 and recdate be considered the time taken from step 1?flexyourdata the date and timestamp in the Step columns are the date and time that it entered that step.
Thanks!
