Time Caculations late/early and summary

I am trying to analyze data output and determine if a employee was late arriving or left early, if so how many times and how late or early they were. The issue I seem to have the most trouble with is it is an overnight shift and I cannot discover the logic to handle shifts covering multiple days. The dates and times are weird because the output is broken from the program I am using and this was the only way to correct

Agent Name Log On DateTime Log On Duration Logout DateTime Log In (PST) Date In Log Out (PST) Date Out Scheduled start (PST) Scheduled end (PST) Late or early and how much? Number of times Late?
Smith, John 9/5/2015 3:46 0:24:31 9/5/2015 4:11 5 9 2015 3:46:56 5/9/2015 5 9 2015 4:11:27 5/9/2015 19:00:00 4:00:00
Smith, John 9/5/2015 19:09 0:19:57 9/5/2015 19:29 5 9 2015 19:09:46 5/9/2015 5 9 2015 19:29:43 5/9/2015 19:00:00 4:00:00
Smith, John 9/5/2015 19:32 0:09:31 9/5/2015 19:41 5 9 2015 19:32:14 5/9/2015 5 9 2015 19:41:45 5/9/2015 19:00:00 4:00:00
Smith, John 9/5/2015 19:45 0:12:09 9/5/2015 19:57 5 9 2015 19:45:01 5/9/2015 5 9 2015 19:57:10 5/9/2015 19:00:00 4:00:00
Smith, John 9/5/2015 20:08 0:09:16 9/5/2015 20:17 5 9 2015 20:08:18 5/9/2015 5 9 2015 20:17:34 5/9/2015 19:00:00 4:00:00
Smith, John 9/5/2015 20:20 0:04:56 9/5/2015 20:25 5 9 2015 20:20:30 5/9/2015 5 9 2015 20:25:26 5/9/2015 19:00:00 4:00:00
Smith, John 13/05/15 03:18:39 0:58:50 13/05/15 04:17:29 5 13 2015 3:18:39 5/13/2015 5 13 2015 4:17:29 5/13/2015 19:00:00 4:00:00
Smith, John 13/05/15 19:24:00 0:07:42 13/05/15 19:31:42 5 13 2015 19:24:00 5/13/2015 5 13 2015 19:31:42 5/13/2015 19:00:00 4:00:00
Smith, John 13/05/15 19:48:28 0:01:34 13/05/15 19:50:02 5 13 2015 19:48:28 5/13/2015 5 13 2015 19:50:02 5/13/2015 19:00:00 4:00:00
Smith, John 13/05/15 20:34:52 1:17:22 13/05/15 21:52:14 5 13 2015 20:34:52 5/13/2015 5 13 2015 21:52:14 5/13/2015 19:00:00 4:00:00
Smith, John 13/05/15 22:30:12 0:03:24 13/05/15 22:33:36 5 13 2015 22:30:12 5/13/2015 5 13 2015 22:33:36 5/13/2015 19:00:00 4:00:00
Smith, John 14/05/15 21:20:05 2:05:25 14/05/15 23:25:30 5 14 2015 21:20:05 5/14/2015 5 14 2015 23:25:30 5/14/2015 19:00:00 4:00:00
Smith, John 14/05/15 23:28:20 0:31:44 15/05/15 00:00:04 5 14 2015 23:28:20 5/14/2015 5 15 2015 0:00:04 5/15/2015 19:00:00 4:00:00
Smith, John 15/05/15 03:51:18 0:24:39 15/05/15 04:15:57 5 15 2015 3:51:18 5/15/2015 5 15 2015 4:15:57 5/15/2015 19:00:00 4:00:00



any help in solving my dilemma would be appreciated. This would need to be able to scale to a larger dataset than pictured.

It should not be difficult to resolve your issue, but not the way you have posted it.

Please attach the data as a workbook so that we can see that you are talking about - properly broken into columns.

Also, saying that your location is "here" is uninformative. This community is from all over the world! We like to know where our fellow Excel users are from. It also helps because of time zone differences! It might help to prioritize responses!



Regards,

David

Please remember, your request is clearer if you attach a sample workbook.
- Click on Go Advanced and click on the Paper Clip.

If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.

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

If several people have responded, when you reply please make it clear WHO you are responding
to by mentioning their name.

Sorry for the abbreviated prior post I was at work and trying to accomplish to much at once. I will scrub the data I have and attach a sample workbook as soon as I can this morning. Thank you both for your replies.

I have attached the partial report to this ticket.
Columns A through D is the data as presented by my reporting tool
I had to use text to columns to parse the data into Rows E through N as it was not in a consistent output (see 9B and above compared to 10B and below)
There are multiple logins so I will also have to find the logic to isolate the earliest login and latest logout for a given day and report on that.
I need a summary of how many times a specific agent was late/early and the average times.

I was just leaving the data as I manipulated it so the process so far could be seen. If there is a way to manipulate the data in B and D without parsing into E through N that would work as well. The problem I was trying to solve with that was the inconsistent output in B and D.

I have been having a look at your problem, and the suggestions posted by newdoverman and have a couple of observations regarding the date.

To my way of looking at things, it seems unlikely that Jesse Pinkman logged on or off at all those times on 5/09 and 13/05! Is it possible Jesse started or ended on those dates, and the dates are wrong, or were two shifts worked?

I look at the data for 5/09, and if I ignore the dates (because I assume that some must be wrong), Jesse clocked on 9 minutes late for that shift (which should have started at 19:00), and clocked off 11 minutes late.

The solution proposed by newdoverman it neat, but I ws worried about the two very late start times, and wondered about a date issue.

I hope this helps, please let me know!

Regards,

David

If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.

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

If several people have responded, when you reply please make it clear WHO you are responding
to by mentioning their name.