Good evening friends,
I am looking to build a digital version of the ancient manual logbooks currently used to track personnel entering/exiting our facility. I currently have badges with barcodes and scanners that capture personal information (Name, employee ID number, department), and use a formula to create a timestamp for when the badge is scanned (Data collection is operational). Because of the frequency of employees and visitors entering and exiting the facility, I'd like to do two things;
1) translate some of this timestamp data to indicate whether an employee or visitor is "In" or "Out" of the building,
2) Create and record a list of visitors who have entered the building during that day (I have 5 employees, and may have up to 15 visitors per day that will enter/depart numerous times. Because of this I'd like to create a list of 'unique' employee ID numbers and their associated names).
Currently I can create a "Dashboard" which displays the status of all known employees...I am using a CountIf formula which counts the number of times an employee has scanned their badge within a given time period; if the result is 0 or an even number, the employee is "Out" of the building, if the result is even the employee is "in" the building. This is logical however, I'd still like to be able to record the individual status of each check-in or check-out based upon previous entries. How do you suggest I do that (depicted in column E of the attached)?
Secondly, I have no idea how to lookup and create a list of unique visitors who are not employees from another list. Any help would be appreciated.
Thanks in advance!
-Mike
p.s. I just realized I am not authorized to post attachments....
I am looking to build a digital version of the ancient manual logbooks currently used to track personnel entering/exiting our facility. I currently have badges with barcodes and scanners that capture personal information (Name, employee ID number, department), and use a formula to create a timestamp for when the badge is scanned (Data collection is operational). Because of the frequency of employees and visitors entering and exiting the facility, I'd like to do two things;
1) translate some of this timestamp data to indicate whether an employee or visitor is "In" or "Out" of the building,
2) Create and record a list of visitors who have entered the building during that day (I have 5 employees, and may have up to 15 visitors per day that will enter/depart numerous times. Because of this I'd like to create a list of 'unique' employee ID numbers and their associated names).
Currently I can create a "Dashboard" which displays the status of all known employees...I am using a CountIf formula which counts the number of times an employee has scanned their badge within a given time period; if the result is 0 or an even number, the employee is "Out" of the building, if the result is even the employee is "in" the building. This is logical however, I'd still like to be able to record the individual status of each check-in or check-out based upon previous entries. How do you suggest I do that (depicted in column E of the attached)?
Secondly, I have no idea how to lookup and create a list of unique visitors who are not employees from another list. Any help would be appreciated.
Thanks in advance!
-Mike
p.s. I just realized I am not authorized to post attachments....
