Trying to get the min and max logins of employees

[MAI1976]

Hello! Below is a sample of some excel data. Trying to figure out how to get this into a report for logins and logouts with their multiple login and logout times. I need per agent / per day the earliest time they logged in that day (Agent-Login) and the latest time they logged out (Agent-Logout). I'm stumped. Thanks!

Date OriginatorID Description StartingTime
05/02/2019 1003 Agent-Login 2:05:56 PM
05/02/2019 1003 Agent-Login 2:11:06 PM
05/02/2019 1003 Agent-Login 2:51:58 PM
05/02/2019 1003 Agent-Login 2:53:52 PM
05/02/2019 1003 Agent-Login 3:07:09 PM
05/02/2019 1003 Agent-Login 3:28:58 PM
05/02/2019 1003 Agent-Logout 2:07:47 PM
05/02/2019 1003 Agent-Logout 2:12:07 PM
05/02/2019 1003 Agent-Logout 2:53:33 PM
05/02/2019 1003 Agent-Logout 2:57:17 PM
05/02/2019 1003 Agent-Logout 3:13:03 PM
05/02/2019 1017 Agent-Login 12:51:33 PM
05/02/2019 1017 Agent-Logout 12:21:37 AM
05/02/2019 1017 Agent-Logout 3:55:30 PM
05/02/2019 1028 Agent-Login 11:12:44 AM
05/02/2019 1028 Agent-Logout 4:51:17 PM
05/02/2019 1037 Agent-Login 9:58:40 AM
05/02/2019 1037 Agent-Logout 6:31:29 PM
05/02/2019 1043 Agent-Login 12:00:16 PM
05/02/2019 1043 Agent-Logout 8:31:17 PM
05/02/2019 1233 Agent-Login 3:11:32 PM
05/02/2019 1233 Agent-Login 3:47:44 PM
05/02/2019 1233 Agent-Logout 3:26:01 PM
05/02/2019 1387 Agent-Login 9:00:13 AM
05/02/2019 1387 Agent-Logout 6:00:03 PM
05/02/2019 1492 Agent-Login 8:51:59 AM
05/02/2019 1492 Agent-Login 11:30:42 AM


An example would be creating another table that would give this as an output :

Agent Date Log in Logout
1003 05/02/2019 2:05:56 PM 3:13:03 PM

[KOKOSEK]

As I see you've got data in 4 columns, so into E2 put:

Please Login or Register  to view this content.

and drag it down as long as need to get full list of log numbers (0 should be the last)
and into F2:

Please Login or Register  to view this content.

and drag it down as long as needed.

[David A Coop]

@KOKOESK provided a satisfactory solution for column F to get the unique agent numbers, but the second half doesn't answer your problem I suspect.

I suggest you create two adjacent column headings with Agent-Login and Agent-Logout (identical to the assumed column c.

Then in column F:

Please Login or Register  to view this content.

and Column G:

Please Login or Register  to view this content.

All the data you provided was for one date. If you need more than one date in the model, you will need to expand add another criteria to the MAXIFS and MINIFS formulae.

I hope this helps, please let me know!

Regards,

David

If this has been helpful
- Please click on the *Add Reputation button at the bottom left.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

[MAI1976]

Thanks Kokosek! I should have been more clear with my question as I needed the earliest login time and latest login time per employee / day. I modified my ask. Thanks for the reply though.

[MAI1976]

Thanks David! I don't think the maxifs / minifs work with 2013. I tried using the min if and max if but I can't seem to get it working. Below is some data with multiple dates. Hoping you can help!! Thanks.

Outpout would start from second ID.

Date ID Description StartingTime ID Date Agent-Login Agent-Logout
05/02/2019 1003 Agent-Login 2:05:56 PM 1003 05/02/2019 2:05:56 PM 3:28:58 PM
05/02/2019 1003 Agent-Login 2:11:06 PM
05/02/2019 1003 Agent-Login 2:51:58 PM
05/02/2019 1003 Agent-Login 2:53:52 PM
05/02/2019 1003 Agent-Login 3:07:09 PM
05/02/2019 1003 Agent-Login 3:28:58 PM
05/02/2019 1003 Agent-Logout 2:07:47 PM
05/02/2019 1003 Agent-Logout 2:12:07 PM
05/02/2019 1003 Agent-Logout 2:53:33 PM
05/02/2019 1003 Agent-Logout 2:57:17 PM
05/02/2019 1003 Agent-Logout 3:13:03 PM
05/02/2019 1017 Agent-Login 12:51:33 PM
05/02/2019 1017 Agent-Logout 12:21:37 AM
05/02/2019 1017 Agent-Logout 3:55:30 PM
06/02/2019 1003 Agent-Login 8:55:57 AM
06/02/2019 1003 Agent-Login 9:04:49 AM
06/02/2019 1003 Agent-Login 9:33:55 AM
06/02/2019 1003 Agent-Login 9:36:56 AM
06/02/2019 1003 Agent-Login 9:54:44 AM
06/02/2019 1003 Agent-Login 9:55:01 AM
06/02/2019 1003 Agent-Login 9:57:52 AM
06/02/2019 1003 Agent-Login 10:03:03 AM
06/02/2019 1003 Agent-Login 10:03:36 AM
06/02/2019 1003 Agent-Login 10:07:16 AM
06/02/2019 1003 Agent-Login 10:08:57 AM
06/02/2019 1003 Agent-Login 10:26:51 AM
06/02/2019 1003 Agent-Login 10:46:22 AM
06/02/2019 1003 Agent-Login 10:53:35 AM
06/02/2019 1003 Agent-Login 11:00:20 AM
06/02/2019 1003 Agent-Login 11:01:51 AM
06/02/2019 1003 Agent-Login 11:07:00 AM
06/02/2019 1003 Agent-Login 11:11:01 AM
06/02/2019 1003 Agent-Login 11:12:12 AM
06/02/2019 1003 Agent-Login 11:39:51 AM
06/02/2019 1003 Agent-Login 11:42:48 AM
06/02/2019 1003 Agent-Login 11:59:00 AM
06/02/2019 1003 Agent-Login 12:01:47 PM
06/02/2019 1003 Agent-Login 12:05:50 PM
06/02/2019 1003 Agent-Login 2:59:55 PM
06/02/2019 1003 Agent-Login 3:10:58 PM
06/02/2019 1003 Agent-Logout 8:01:47 AM

[KOKOSEK]

Please Login or Register  to view this content.

where X is cell with login number and Y particular date

[KOKOSEK]

Check attached file. I think we are direct to that.

[MAI1976]

Thanks so much! Exactly what I needed!

[KOKOSEK]

You welcome.
If You are happy with solution, please use thread tools and marl thread as SOLVED.

p.s. sort date column asc, it gives you as result date in table sorted.