Timesheet : Calculate time worked with multiple Ins and Out

[VBA_Local]

Hey excel Gurus,
Long time lurker who's learnt alot from you in the past - now creating an account to post one that stumped me !

So i have a database of time Ins and Outs, per day. calculating the time between First in and Last out is easy, Time(a1-b1, "hh") format.
Where it gets complicated, is multiple Time Ins and Outs, within the same day

Below is an example :


Date Time Location Remarks
1-Sep-20 9:40 Main Staff Entry Car Barrier (IN) First in, time starts from now "This is easy, time spent for the day is 7:05 = 16:45 (-) 9:40"
1-Sep-20 9:50 Entry Barrier L1 (IN) Ignore this, since it's 2nd In
1-Sep-20 9:51 Staff Entry Barrier L3 (IN) Ignore this, since it's 3rd In
1-Sep-20 16:45 Staff Entry Barrier L1 (OUT) First Out, time captured
1-Sep-20 16:45 Staff Entry Barrier L3 (OUT) Ignore since 2nd Out.

New day

2-Sep-20 12:48 Main Staff Entry Car Barrier (IN) First in, time starts from now This is tricky, time is total 5:36, calculated as : ((3:02 = 15:50-12:48)+ (2:34 = 18:28 - 15:50))
2-Sep-20 12:55 Staff Entry Barrier L3 (IN) Ignore this, since it's 2nd In
2-Sep-20 15:50 Staff Entry Barrier L1 (OUT) Capture this, since it's 1st out
2-Sep-20 15:54 Staff Entry Barrier L3 (IN) Capture this since its 1st In Again
2-Sep-20 18:28 Staff Entry Barrier L1 (OUT) Capture this since its 2nd Out again

New day

3-Sep-20 8:25 Main Staff Entry Car Barrier (IN) First in, time starts from now Another Trickey one. Total time is 7:05 = ((2:01=10:26-8:25)+(5:04=15:32-10:28))
3-Sep-20 8:32 Staff Entry Barrier L3 (IN) Ignore this, since it's 2nd In
3-Sep-20 10:26 Staff Entry Barrier L1 (OUT) Again, Capture this since it's 1st Out
3-Sep-20 10:27 Main Staff Exit _ Executive Gate (OUT) Ignore since 2nd Out.
3-Sep-20 10:28 Main Staff Entry _ Executive Gate (IN) Capture again, 2nd In for the day
3-Sep-20 10:29 Staff Entry Barrier L3 (IN) Ignore, 2nd In at new gate
3-Sep-20 15:32 Staff Entry Barrier L1 (OUT) Capture final out


Any leads or suggestions >? I'm sure there's a way with Sumproduct (a formula i detest) or a nested if. Please do let me know
I've attached the file and a snapshot

Thanks
Localmode