Sum Times based on StatusID & EmpID

N

noname

Hi,

I have a columns for EmpID, StatusID(1 for Login, 2 for Logout) &
Times(DateTime Stamp).

EmpID StatusID Times
Difference
------------------------------------------------------------------------------
1 1 4/2/07 8:32 AM
1 2 4/2/07 8:33 AM
1 1 4/2/07 8:37 AM
1 2 4/2/07 8:59 AM
1 1 4/2/07 9:11 AM
1 2 4/3/07 9:12 AM
1 2 4/3/07 9:48 AM error
1 1 4/3/07 9:49 AM
1 2 4/3/07 11:02 AM
2 1 4/3/07 6:02 PM
2 2 4/3/07 6:03 PM
2 2 4/3/07 6:04 PM error
2 1 4/3/07 6:22 PM
2 2 4/3/07 6:23 PM
3 2 4/4/07 8:37 AM error
3 2 4/4/07 8:38 AM error
3 2 4/4/07 8:39 AM error
3 2 4/4/07 12:11 PM error
3 1 4/4/07 12:12 PM
3 2 4/4/07 12:17 PM
3 1 4/4/07 12:23 PM
3 2 4/4/07 1:14 PM


In the difference column, i need to subtract the last logout from
First Login, excluding any errors which may have occurred due to
multiple logins or multiple logouts. Please note i need to mark out
errors someway, such that for every login (1) there should be a
corresponding logout below. Any other extra logins or logouts occuring
below should be marked as errors.

On another sheet, i want to summarise data.

Date EmpID FirstLogin LastLogout
Difference
-----------------------------------------------------------------------------------------
4/2/07 1
4/3/07 1
4/3/07 2
4/4/07 3

How to do accomplish this????

Any brainiacs out there who know how to achieve this using worksheet
functions or VBA?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Another Time Related Question 7
Outlook 2010 can't find .pst file 1
Calculating Early Login & Last Logout 1
Extract first and last login time for each day only 6
Gears 14
Runtime error 13 : type mismatch 2
List all combinations of 6/36 with unique 4 numbers 26
If Function 1

Top