Barcode Scanning & Clean Attendance Sheet - Organizing Time In and Time Out data

[RyleeEman]

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.

Example Scenario

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?

[#2]

@RyleeEman 

I decided to first focus on TIME IN.

The TIME IN (Column O) on "Day 1 Afternoon" formula should be along the lines of:

Look up A5 (barcode) on "For Scanning Barcodes" sheet in columns A through E and look for the timestamp in column D (or 4) whose time value is greater than 13:40 and less than 13:50.

(Formula Attempt 1, TIME IN - "" is the result)

IF TIMESTAMP IS GREATER THAN 13:40 AND TIMESTAMP IS LESS THAN 13:50

=IFERROR(VLOOKUP(A5,'For Scanning Barcodes'!$A:$E,IF(AND(VLOOKUP(A5,'For Scanning Barcodes'!$A:$E,4,0)>TIME(13,40,0),VLOOKUP(A5,'For Scanning Barcodes'!$A:$E,4,0)<TIME(13,50,0)),4,0),FALSE),"")

(Formula Attempt 2, TIME IN - "" is the result)

IF TIMESTAMP IS LESS THAN 13:50 AND TIMESTAMP IS GREATER THAN 13:40

=IFERROR(VLOOKUP(A5,'For Scanning Barcodes'!$A:$E,IF(AND(VLOOKUP(A5,'For Scanning Barcodes'!$A:$E,4,0)<TIME(13,50,0),VLOOKUP(A5,'For Scanning Barcodes'!$A:$E,4,0)>TIME(13,40,0)),4,0),FALSE),"")

Since these didn't work, I tried just one or the other on greater than and less than.

(Formula Attempt 3, TIME IN - "" is the result)

IF TIMESTAMP IS LESS THAN 13:50

=IFERROR(VLOOKUP(A5,'For Scanning Barcodes'!$A:$E,IF(VLOOKUP(A5,'For Scanning Barcodes'!$A:$E,4,0)<TIME(13,50,0),4,0),FALSE),"")

(Formula Attempt 4, TIME IN - 1st timestamp of barcode (13:45) is the result)

IF TIMESTAMP IS GREATER THAN 13:40

=IFERROR(VLOOKUP(A5,'For Scanning Barcodes'!$A:$E,IF(VLOOKUP(A5,'For Scanning Barcodes'!$A:$E,4,0)>TIME(13,40,0),4,0),FALSE),"")

When Formula Attempt 4 resulted in the proper barcode timestamp (the first scan when signing in at 13:45), I thought I had something. So I tried the same logic on TIME OUT (Column P):

(Formula Attempt 4, TIME OUT - 1st timestamp of barcode (13:45) is the result, not correct)

IF TIMESTAMP IS GREATER THAN 14:25

=IFERROR(VLOOKUP(A5,'For Scanning Barcodes'!$A:$E,IF(VLOOKUP(A5,'For Scanning Barcodes'!$A:$E,4,0)>TIME(14,25,0),4,0),FALSE),"")

That's weird, right? Why is this still looking at a time value that is less than 14:25?

Row 5: Attempt 1 TIME IN, Attempt 4 TIME OUT

Row 6: Attempt 2 TIME IN, Attempt 4 TIME OUT

Row 7: Attempt 3 TIME IN, Attempt 4 TIME OUT

Row 8: Attempt 4 TIME IN, Attempt 4 TIME OUT

[#3]

@RyleeEman 

Solved / Solution Verified

See mh_mike response on my Reddit post:

https://www.reddit.com/r/excel/comments/b4ba33/barcode_scanning_clean_attendance_sheet/

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)