I use excel in conjunction with a barcode scanner to create attendance sheets that have sign in / sign out data for live classes. In my excel file, I currently work with two sheets: one that timestamps when attendees scan their barcode ("For Scanning Barcodes"), and the other that has attendee information for the previous sheet to reference ("Names and Numbers").
From the data inputted on the "For Scanning Barcodes" sheet, I create a clean attendance sheet that shows each attendee's sign in and sign out times. Currently, my clean attendance sheets are created manually by copying and pasting from "For Scanning Barcodes" during certain time slots, deleting duplicates (because our attendees sometimes want to make doubly sure they are checked in). This is tedious, and learning more about Excel formulas got me thinking, "There's got to be a better way!" lol
So, I've been making a third sheet, which is labeled to pertain to the day and when the class occurred, i.e."Day 1 Afternoon." This sheet focuses on organizing the timestamp data from "For Scanning Barcodes" so that I will have an automatically clean attendance sheet that shows clear Time In and Time Out for each attendee.
I've already set up the sheet so that it has each registered attendee's barcode (Column A, starting at A5) and their information.I want to create formulas for TIME IN (Column O) and TIME OUT (Column P) that will look at the attendee's barcode in Column A on "Day 1 Afternoon," and reference it in the "For Scanning Barcodes" sheet to find the barcode's timestamp during certain time slots. However, I'm struggling with a formula that works correctly.
The class starts at 13:45 and ends at 14:30. We give our attendees a window of 10 minutes to sign in, from 13:40 to 13:50, and sometimes let them out 5 minutes early (14:25).
How would you create a formula for TIME IN and TIME OUT?
For those who are also making attendance record using Excel and barcode scanning for signing in and signing out, attached is my .txt file of all formulas used for "For Barcode Scanning" sheet (data being inputted) and "Attendance Sheet" sheet (data being organized)