Identify previous steps in table

[Pestomania]

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!

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")),"")