Hi
I have an in & Out time of employees and I need to find out , how much time they have spent out of a particular floor on a particular day.
Unique field are date and the card number which is used at entry and exit point of the floor. There is a time field which provides time spent in or outside the floor.
'reader in' means employee entered and 'reader out' means employee went out. The time difference on a particular date's 'reader in' & 'reader out' will
provide the actual time spent out of a particular floor. Which in my case is the 9th floor. Which means how much time an employee spent in a day out of 09th floor.
The data is for entire month and for multiple employees (which could be identifyied basis unique "card number".
The spread sheet looks like this, Date is in column A , Time in b , Reader in C, Card Number in D, name in E :
Date Time Reader Card Number Name
4/1/2015 10:01:00 AM 9 FLOOR RECEPTION C 14 - Reader IN 41363 XYZ
4/1/2015 11:53:00 AM 9 FLOOR FIRE EXIT C13 - Reader OUT 41363 XYZ
4/1/2015 11:54:00 AM 11 FLOOR FIRE EXIT - Reader IN 41363 XYZ
4/1/2015 12:05:00 PM 11 FLOOR FIRE EXIT - Reader OUT 41363 XYZ
4/1/2015 12:06:00 PM 9 FLOOR FIRE EXIT C13 - Reader IN 41363 XYZ
4/1/2015 2:16:00 PM 9 FLOOR FIRE EXIT C13 - Reader OUT 41363 XYZ
4/1/2015 2:17:00 PM 11 FLOOR FIRE EXIT - Reader IN 41363 XYZ
4/1/2015 2:39:00 PM 11 FLOOR FIRE EXIT - Reader OUT 41363 XYZ
4/1/2015 2:40:00 PM 9 FLOOR FIRE EXIT C13 - Reader IN 41363 XYZ
4/1/2015 2:42:00 PM 9 FLOOR RECEPTION C 14 - Reader OUT 41363 XYZ
4/1/2015 3:07:00 PM 9 FLOOR RECEPTION C 14 - Reader IN 41363 XYZ
4/1/2015 7:13:00 PM 9 FLOOR RECEPTION C 14 - Reader OUT 41363 XYZ
4/1/2015 8:48:00 AM 9 FLOOR RECEPTION C 14 - Reader IN 6481 ABC
4/1/2015 10:56:00 AM 9 FLOOR RECEPTION C 14 - Reader OUT 6481 ABC
4/1/2015 11:06:00 AM 9 FLOOR RECEPTION C 14 - Reader IN 6481 ABC
4/1/2015 11:37:00 AM 9 FLOOR RECEPTION C 14 - Reader OUT 6481 ABC
4/1/2015 11:49:00 AM 9 FLOOR RECEPTION C 14 - Reader IN 6481 ABC
4/1/2015 3:30:00 PM 9 FLOOR RECEPTION C 14 - Reader IN 6481 ABC
4/1/2015 6:00:00 PM 9 FLOOR RECEPTION C 14 - Reader OUT 6481 ABC
4/1/2015 10:06:00 AM 9 FLOOR RECEPTION C 14 - Reader IN 6894 EFG
4/1/2015 1:07:00 PM 9 FLOOR FIRE EXIT C13 - Reader OUT 6894 EFG
4/1/2015 1:08:00 PM 11 FLOOR FIRE EXIT - Reader IN 6894 EFG
4/1/2015 1:22:00 PM 11 FLOOR FIRE EXIT - Reader OUT 6894 EFG
4/1/2015 1:23:00 PM 9 FLOOR FIRE EXIT C13 - Reader IN 6894 EFG
4/1/2015 2:15:00 PM 9 FLOOR RECEPTION C 14 - Reader OUT 6894 EFG
4/1/2015 2:49:00 PM 9 FLOOR RECEPTION C 14 - Reader IN 6894 EFG
4/1/2015 7:12:00 PM 9 FLOOR RECEPTION C 14 - Reader OUT 6894 EFG
As an end out put following is required, for each employee, date wise :
Date | Card number | name | total time out of 9th floor | time spent on other floors |
Request if anyone in the forum lets me know if this could be done in excel and help me in getting this. Pls note, I use excel 2002 version.
Regards.
Leena
I have an in & Out time of employees and I need to find out , how much time they have spent out of a particular floor on a particular day.
Unique field are date and the card number which is used at entry and exit point of the floor. There is a time field which provides time spent in or outside the floor.
'reader in' means employee entered and 'reader out' means employee went out. The time difference on a particular date's 'reader in' & 'reader out' will
provide the actual time spent out of a particular floor. Which in my case is the 9th floor. Which means how much time an employee spent in a day out of 09th floor.
The data is for entire month and for multiple employees (which could be identifyied basis unique "card number".
The spread sheet looks like this, Date is in column A , Time in b , Reader in C, Card Number in D, name in E :
Date Time Reader Card Number Name
4/1/2015 10:01:00 AM 9 FLOOR RECEPTION C 14 - Reader IN 41363 XYZ
4/1/2015 11:53:00 AM 9 FLOOR FIRE EXIT C13 - Reader OUT 41363 XYZ
4/1/2015 11:54:00 AM 11 FLOOR FIRE EXIT - Reader IN 41363 XYZ
4/1/2015 12:05:00 PM 11 FLOOR FIRE EXIT - Reader OUT 41363 XYZ
4/1/2015 12:06:00 PM 9 FLOOR FIRE EXIT C13 - Reader IN 41363 XYZ
4/1/2015 2:16:00 PM 9 FLOOR FIRE EXIT C13 - Reader OUT 41363 XYZ
4/1/2015 2:17:00 PM 11 FLOOR FIRE EXIT - Reader IN 41363 XYZ
4/1/2015 2:39:00 PM 11 FLOOR FIRE EXIT - Reader OUT 41363 XYZ
4/1/2015 2:40:00 PM 9 FLOOR FIRE EXIT C13 - Reader IN 41363 XYZ
4/1/2015 2:42:00 PM 9 FLOOR RECEPTION C 14 - Reader OUT 41363 XYZ
4/1/2015 3:07:00 PM 9 FLOOR RECEPTION C 14 - Reader IN 41363 XYZ
4/1/2015 7:13:00 PM 9 FLOOR RECEPTION C 14 - Reader OUT 41363 XYZ
4/1/2015 8:48:00 AM 9 FLOOR RECEPTION C 14 - Reader IN 6481 ABC
4/1/2015 10:56:00 AM 9 FLOOR RECEPTION C 14 - Reader OUT 6481 ABC
4/1/2015 11:06:00 AM 9 FLOOR RECEPTION C 14 - Reader IN 6481 ABC
4/1/2015 11:37:00 AM 9 FLOOR RECEPTION C 14 - Reader OUT 6481 ABC
4/1/2015 11:49:00 AM 9 FLOOR RECEPTION C 14 - Reader IN 6481 ABC
4/1/2015 3:30:00 PM 9 FLOOR RECEPTION C 14 - Reader IN 6481 ABC
4/1/2015 6:00:00 PM 9 FLOOR RECEPTION C 14 - Reader OUT 6481 ABC
4/1/2015 10:06:00 AM 9 FLOOR RECEPTION C 14 - Reader IN 6894 EFG
4/1/2015 1:07:00 PM 9 FLOOR FIRE EXIT C13 - Reader OUT 6894 EFG
4/1/2015 1:08:00 PM 11 FLOOR FIRE EXIT - Reader IN 6894 EFG
4/1/2015 1:22:00 PM 11 FLOOR FIRE EXIT - Reader OUT 6894 EFG
4/1/2015 1:23:00 PM 9 FLOOR FIRE EXIT C13 - Reader IN 6894 EFG
4/1/2015 2:15:00 PM 9 FLOOR RECEPTION C 14 - Reader OUT 6894 EFG
4/1/2015 2:49:00 PM 9 FLOOR RECEPTION C 14 - Reader IN 6894 EFG
4/1/2015 7:12:00 PM 9 FLOOR RECEPTION C 14 - Reader OUT 6894 EFG
As an end out put following is required, for each employee, date wise :
Date | Card number | name | total time out of 9th floor | time spent on other floors |
Request if anyone in the forum lets me know if this could be done in excel and help me in getting this. Pls note, I use excel 2002 version.
Regards.
Leena