Forum Discussion
Barcode Scanning & Clean Attendance Sheet - Organizing Time In and Time Out data
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
