Need to extract In & Out time of employees

[leena24]

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

 

[leena24]

This works for me ! Many thanks Andrew ! . Thanks for the tip Rick, will be mindful in future.
Mods please close this thread as resolved !

 

[Rick Rothstein]

Thanks for the tip Rick, will be mindful in future.

Actually, my comment was made for yky, as well as other volunteers who answer question here on this forum... it really has no practical application for you as a questioner. What would apply to you is to use an add-in like the one I give a link for in my signature line... that will allow you to capture a copy of a selected range of cells from a worksheet and paste it into your question or reply window here in this forum... that would give us a clearer picture of your data layout and also allow us to copy/paste it into our own worksheets for testing purposes.