-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.

Dave
  • 25,513
Mrutyunjaya Mohapatra
  • 1

1 Answers1

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)
NeronLeVelu
  • 150