Scan Bardcode to Excel, Track Time in/out and tag Status

[ah_seah]

Hi everyone! I need some help on excel here...

Cell B2 - Manual entry of class start time
Cell A4 - Scan in ID (skips to next row A5 after A4 is populated and so on). if ID is already scanned before (i.e Entry Captured), it will go to the cell in column A with correct ID to populate the next exit or entry date/time)
Cell B4 - Populate name based on information in <database> tab

Cell C4 - Capture the date and time of first entry
Cell D4 - Capture the date and time of first exit
Cell E4 - Capture the date and time of second entry
Cell F4 - Capture the date and time of second exit
Cell G4 - Capture the date and time of third entry
Cell H4 - Capture the date and time of third exit

Cell J4 - Calculate the duration this person was not present
Cell K4 - Calculate the attendance status of this student based on below pre-defined conditions.

These are the conditions for Column K for determining the attendance status:
1. If the student enters after class start time in B2 = Late
2. If the student is away for more than 30 minutes for entire class = Partial
3. If the student fulfils above two conditions = Late and Partial
4. If above conditions do not apply to student = Present

The thread below (reply #18 ) is very similar to what I need but with some modifications based on the above:
https://www.mrexcel.com/forum/excel-questions/672492-scan-barcode-excel-date-time-stamp-out-2.html

The excel file will look like this - https://www.dropbox.com/s/oz5bhbip4nilzue/attendance scan.xlsx?dl=0

Is anyone able to help? Thank you!

Regards, ah_seah