Pestomania
Active Member
- Joined
- May 30, 2018
- Messages
- 332
- Office Version
- 365
- Platform
- Windows
Hello all,
I am trying to set up a table which will allow me to add smart logic to validate and / or verify the work ticket & step # combinations to identify previous and post step numbers. I am unsure if I explained it well in the below example, but a quick thought is that the table could identify the previous step in the process that was applicable. If it wasn't scanned, it moves to the one prior until something was scanned. I added steps to A12 of what I think explain the criteria for the formula.
I am sure there will be questions, please let me know and thank you in advance for the help!
I am trying to set up a table which will allow me to add smart logic to validate and / or verify the work ticket & step # combinations to identify previous and post step numbers. I am unsure if I explained it well in the below example, but a quick thought is that the table could identify the previous step in the process that was applicable. If it wasn't scanned, it moves to the one prior until something was scanned. I added steps to A12 of what I think explain the criteria for the formula.
I am sure there will be questions, please let me know and thank you in advance for the help!
Book2 | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | |||
1 | Work Ticket Number | Step # | Scanned In Date / Time | Scanned Out Date / Time | Queue Time (dd-h:mm) | WIP (dd-h:mm) | Status | Assumed Date & Time | 7/11/2023 22:30 | |||
2 | 21578 | 10 | 7/1/2023 8:00 | 7/1/2023 10:30 | 00-2:30 | Complete | ||||||
3 | 21578 | 20 | 7/2/2023 1:00 | 7/2/2023 6:00 | 00-14:30 | 00-5:00 | Complete | |||||
4 | 21578 | 30 | 7/2/2023 17:00 | 7/2/2023 19:00 | 00-11:00 | 00-2:00 | Complete | |||||
5 | 21578 | 40 | 7/3/2023 3:00 | 7/5/2023 22:00 | 00-8:00 | 02-19:00 | Complete | |||||
6 | 21578 | 50 | Missed Scan | |||||||||
7 | 21578 | 60 | 7/11/2023 8:00 | 05-10:00 | 00-14:30 | Missed Scan | ||||||
8 | 21578 | 70 | 7/11/2023 21:00 | 11-21:00 | 00-1:30 | In Progress | ||||||
9 | 21578 | 80 | ||||||||||
10 | 21578 | 90 | ||||||||||
11 | ||||||||||||
12 | Steps: 1. Determine the "previous step" with the work ticket combo. In this case step 30 was the previous step to 40. 2. Identify the queue time in the above format which is how long was it setting from "scanned out" on previous step to "scanned in" on the current step 3. Identify the WIP time. This is the difference between scanned out & scanned in. If scanned out is empty, the WIP time is based on current date and time value (symbolized by J1 in this example. 4. If scanned out and scanned in are not blank, status is complete. If the next step is scanned in but there is a missing scan on a step, then it is a "Missed Scan" (this example step 50 is a missed scan because someone scanned into step 60). If Scanned In and no future steps have been scanned, that step is "in progress". | |||||||||||
13 | ||||||||||||
14 | ||||||||||||
15 | ||||||||||||
16 | ||||||||||||
17 | ||||||||||||
18 | ||||||||||||
19 | ||||||||||||
20 | ||||||||||||
21 | ||||||||||||
22 | ||||||||||||
23 | ||||||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
F2:F8 | F2 | =IF(C2<>"",IF(D2<>"",TEXT(D2-C2,"dd-h:mm"),TEXT($J$1-C2,"dd-h:mm")),"") |
G2:G8 | G2 | =IF(D2<>"","Complete",IF(AND(C2<>"",NOT(D2<>""),C3<>""),"Missed Scan",IF(AND(NOT(C2<>""),NOT(D2<>""),C3<>""),"Missed Scan",IF(AND(C2<>"",NOT(D2<>"")),"In Progress")))) |
E3:E8 | E3 | =IFERROR(IF(D2<>"",TEXT(C3-D2,"dd-h:mm"),TEXT(C3-D1,"dd-h:mm")),"") |