-1

I have an excel sheet containing the Entry and Exit time for users from bio metric software. I want to find how much time the user had stayed i.e. sum of difference between entry and exit times, but some time the user has to puch twice as the bio metric software doesn't accepts its login(like line 3 & 4 and 9 & 10 in the below shown table). In that case the difference should be done taking the last repeting Entry and last repeating Exit.

Please refer the snapshot below:

   Date/Time                      Entry/Exit        Badge       Reader              
    7/10/2014       09:36:46        Entry           773821      BAN-IOS-2F-IBS-TS-IN NEW    
    7/10/2014       11:22:42        Exit            773821      BAN-IOS-2F-IBS-TS-OUT NEW   
    7/10/2014       11:27:34        Entry           773821      BAN-IOS-2F-IBS-TS-IN NEW    
    7/10/2014       11:27:42        Entry           773821      BAN-IOS-2F-IBS-TS-IN NEW    
    7/10/2014       12:59:24        Exit            773821      BAN-IOS-2F-IBS-TS-OUT NEW   
    7/10/2014       13:57:10        Entry           773821      BAN-IOS-2F-IBS-TS-IN NEW    
    7/10/2014       15:56:42        Exit            773821      BAN-IOS-2F-IBS-TS-OUT NEW   
    7/10/2014       16:24:19        Entry           773821      BAN-IOS-2F-IBS-TS-IN NEW    
    7/10/2014       17:25:56        Exit            773821      BAN-IOS-2F-IBS-TS-OUT NEW   
    7/10/2014       17:26:56        Exit            773821      BAN-IOS-2F-IBS-TS-OUT NEW   

Please help in calculating the time the user has stayed in his desk from the data in the excel sheet.

4
  • This can be done using macros but i am not aware of it. Can anyone help? – Mrutyunjaya Mohapatra Dec 18 '14 at 11:52
  • What have you tried so far? Please read How do I ask a good question?. – DavidPostill Dec 18 '14 at 11:58
  • Edit your post. Highlight the data (using the mouse) then click on the code icon to format it. This will make it easier for us to read and help :) Please also provide the expected result from the above sample :) – Dave Dec 18 '14 at 12:00
  • Please find the more clear picture of the question. The snapshot is a screenshot for excel sheet. – Mrutyunjaya Mohapatra Dec 18 '14 at 12:16
0

Add a column (G in my case) with this formula

in cell G2 (same for lower cell of the column and relative to their row)

=IF(C2="exit";G1+B2-B1;0)

Just sum the total (a pivot table will easily sum per date/user)

Assuming

  • first date is on A2
  • entry and exit are on the same day (if not, use a sum of date + entry as value in place of B2 and B1)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.