Excel 2007 : Overlapping Payroll Times

All,

I am in charge of Payroll. I have taken several classes regarding Excel, but I cannot seem to figure this out...

I export 400 employees' clock-in and out times to Excel 2007. I usually manually look for overlapping punches, meaning an employee is getting paid twice for the seconds, minutes, or hours worked.

I do not know how do do this!

Here is what I look at for 400 employees:

Time In Time Out
6/16/2012 22:14 6/17/2012 7:17

Time In is Column D and Time Out is Column E.

The first row is 2 and the last row is 2807.

Help?!

Thanks!

are you giving an example of an "overlapping times", are is that just a generic example? To me, it looks like that person worked the night shift from 10:15 pm to 7:15 am...

How is the data entered? How is the data formatted? Do you want a macro solution, or try to keep it formula-based? Do you have a sample sheet with the erros highlighted for us to look at?

For example, here is an employee. This particular employee is in and out of locaitons. These are his clock in and out times for two weeks:

M-IN072 6/11/2012 BORDEN JOSHUA 6/11/2012 2:49 6/11/2012 6:11 3:22 3.37 0 3.37
K-IN086 6/12/2012 BORDEN JOSHUA 6/12/2012 1:47 6/12/2012 7:01 5:14 5.23 0 5.23
K-IN906 6/12/2012 BORDEN JOSHUA 6/12/2012 0:37 6/12/2012 1:47 1:10 1.17 0 1.17
K-IN940 6/12/2012 BORDEN JOSHUA 6/12/2012 22:59 6/12/2012 23:10 0:11 0.18 0 0.18
K-IN970 6/12/2012 BORDEN JOSHUA 6/12/2012 7:01 6/12/2012 8:05 1:04 1.07 0 1.07
K-IN983 6/12/2012 BORDEN JOSHUA 6/12/2012 23:10 6/13/2012 0:24 1:14 1.23 0 1.23
K-IN115 6/13/2012 BORDEN JOSHUA 6/13/2012 0:24 6/13/2012 1:21 0:57 0.95 0 0.95
K-IN118 6/13/2012 BORDEN JOSHUA 6/13/2012 1:21 6/13/2012 2:24 1:03 1.05 0 1.05
K-IN118 6/13/2012 BORDEN JOSHUA 6/13/2012 23:05 6/14/2012 0:49 1:44 1.73 0 1.73
K-IN500 6/13/2012 BORDEN JOSHUA 6/13/2012 3:14 6/13/2012 5:00 1:46 1.77 0 1.77
K-IN905 6/13/2012 BORDEN JOSHUA 6/13/2012 2:24 6/13/2012 3:14 0:50 0.83 0 0.83
K-IN970 6/13/2012 BORDEN JOSHUA 6/13/2012 5:00 6/13/2012 6:19 1:19 1.32 0 1.32
K-IN500 6/14/2012 BORDEN JOSHUA 6/14/2012 1:56 6/14/2012 4:40 2:44 2.73 0 2.73
K-IN970 6/14/2012 BORDEN JOSHUA 6/14/2012 5:00 6/14/2012 6:30 1:30 1.5 0 1.5
K-IN985 6/14/2012 BORDEN JOSHUA 6/14/2012 0:49 6/14/2012 1:56 1:07 1.12 0 1.12
M-IN042 6/14/2012 BORDEN JOSHUA 6/14/2012 4:40 6/14/2012 5:00 0:20 0.33 0 0.33
K-IN906 6/15/2012 BORDEN JOSHUA 6/15/2012 0:05 6/15/2012 0:26 0:21 0.35 0 0.35
K-IN940 6/15/2012 BORDEN JOSHUA 6/15/2012 3:28 6/15/2012 5:42 2:14 2.23 0 2.23
K-IN970 6/15/2012 BORDEN JOSHUA 6/15/2012 5:42 6/15/2012 6:03 0:21 0.35 0 0.35
M-IN053 6/15/2012 BORDEN JOSHUA 6/15/2012 2:33 6/15/2012 3:28 0:55 0.92 0 0.92
M-IN090 6/15/2012 BORDEN JOSHUA 6/15/2012 0:26 6/15/2012 1:38 1:12 1.2 0 1.2
M-IN209 6/15/2012 BORDEN JOSHUA 6/15/2012 1:38 6/15/2012 2:33 0:55 0.92 0 0.92
K-IN970 6/17/2012 BORDEN JOSHUA 6/17/2012 0:00 6/17/2012 7:10 7:10 7.17 -0.5 6.67
K-IN980 6/17/2012 BORDEN JOSHUA 6/17/2012 23:56 6/18/2012 1:39 1:43 1.72 0 1.72
K-IN066 6/18/2012 BORDEN JOSHUA 6/18/2012 23:52 6/19/2012 0:09 0:17 0.28 0 0.28
K-IN500 6/18/2012 BORDEN JOSHUA 6/18/2012 2:51 6/18/2012 2:52 0:01 0.02 0 0.02
K-IN905 6/18/2012 BORDEN JOSHUA 6/18/2012 23:28 6/18/2012 23:52 0:24 0.4 0 0.4
K-IN906 6/18/2012 BORDEN JOSHUA 6/18/2012 1:58 6/18/2012 2:51 0:53 0.88 0 0.88
K-IN906 6/18/2012 BORDEN JOSHUA 6/18/2012 3:16 6/18/2012 6:47 3:31 3.52 0 3.52
K-IN962 6/18/2012 BORDEN JOSHUA 6/18/2012 2:52 6/18/2012 3:16 0:24 0.4 0 0.4
K-IN970 6/18/2012 BORDEN JOSHUA 6/18/2012 6:47 6/18/2012 7:07 0:20 0.33 0 0.33
M-IN090 6/18/2012 BORDEN JOSHUA 6/18/2012 1:39 6/18/2012 1:58 0:19 0.32 0 0.32
K-IN118 6/19/2012 BORDEN JOSHUA 6/19/2012 0:35 6/19/2012 3:43 3:08 3.13 0 3.13
K-IN905 6/19/2012 BORDEN JOSHUA 6/19/2012 0:09 6/19/2012 0:35 0:26 0.43 0 0.43
K-IN927 6/19/2012 BORDEN JOSHUA 6/19/2012 3:43 6/19/2012 4:48 1:05 1.08 0 1.08
K-IN970 6/19/2012 BORDEN JOSHUA 6/19/2012 6:30 6/19/2012 7:25 0:55 0.92 0 0.92
K-IN998 6/19/2012 BORDEN JOSHUA 6/19/2012 4:48 6/19/2012 6:30 1:42 1.7 0 1.7
K-IN998 6/19/2012 BORDEN JOSHUA 6/19/2012 23:38 6/20/2012 0:04 0:26 0.43 0 0.43
K-IN115 6/20/2012 BORDEN JOSHUA 6/20/2012 6:20 6/20/2012 6:49 0:29 0.48 0 0.48
K-IN118 6/20/2012 BORDEN JOSHUA 6/20/2012 2:44 6/20/2012 6:20 3:36 3.6 0 3.6
K-IN970 6/20/2012 BORDEN JOSHUA 6/20/2012 7:30 6/20/2012 8:47 1:17 1.28 0 1.28
K-IN985 6/20/2012 BORDEN JOSHUA 6/20/2012 6:49 6/20/2012 7:30 0:41 0.68 0 0.68
M-IN042 6/20/2012 BORDEN JOSHUA 6/20/2012 22:46 6/21/2012 1:46 3:00 3 0 3
M-IN072 6/20/2012 BORDEN JOSHUA 6/20/2012 0:04 6/20/2012 2:44 2:40 2.67 0 2.67
K-IN066 6/21/2012 BORDEN JOSHUA 6/21/2012 6:26 6/21/2012 6:45 0:19 0.32 0 0.32
K-IN110 6/21/2012 BORDEN JOSHUA 6/21/2012 5:54 6/21/2012 6:26 0:32 0.53 0 0.53
K-IN118 6/21/2012 BORDEN JOSHUA 6/21/2012 6:45 6/21/2012 8:08 1:23 1.38 0 1.38
K-IN970 6/21/2012 BORDEN JOSHUA 6/21/2012 9:15 6/21/2012 9:19 0:04 0.07 0 0.07
K-IN985 6/21/2012 BORDEN JOSHUA 6/21/2012 8:08 6/21/2012 9:15 1:07 1.12 0 1.12
K-IN992 6/21/2012 BORDEN JOSHUA 6/21/2012 1:46 6/21/2012 5:54 4:08 4.13 0 4.13
K-IN118 6/22/2012 BORDEN JOSHUA 6/22/2012 3:01 6/22/2012 4:49 1:48 1.8 0 1.8
K-IN500 6/22/2012 BORDEN JOSHUA 6/22/2012 0:33 6/22/2012 0:52 0:19 0.32 0 0.32
M-IN072 6/22/2012 BORDEN JOSHUA 6/22/2012 4:49 6/22/2012 6:00 1:11 1.18 0 1.18
M-IN209 6/22/2012 BORDEN JOSHUA 6/22/2012 0:52 6/22/2012 3:01 2:09 2.15 0 2.15
K-IN118 6/24/2012 BORDEN JOSHUA 6/24/2012 22:22 6/25/2012 0:08 1:46 1.77 0 1.77
Total 80.77 -0.5 80.27

I need to find a formula that checks Column D and Column E for overlapping punches. For example, IF on 6/22/12, he had an entry of 2 a.m. to 4 a.m. and another for 2:30 a.m. to 3 a.m. That is not okay because I would be paying him twice for the same thirty minutes. I need to be able to find those overlapping punches.

Hope this helps!

Thank you!!

it might be easier if you upload a sample and not the data...

Would the assumption be true that someone on a given day would always be punched in and there are no unpunched breaks in between, or there's always a set number of breaks for a set amount of time without variation?

If that were so, you could look for the highest time value and subtract the lowest previous time value. I can't tell from your pasted extract because it isn't sorted by time/date but rather by ID

Otherwise it will be very complex

I cannot upload the whole report due to the employees' privacy.

The report is sorted by employees' last name.

Thanks.

I don't think so.

There are people who only work 1 day in 14 days or worked a few times in a 1 day.

Thanks.

Originally Posted by AM Services, Inc.

I don't think so.

There are people who only work 1 day in 14 days or worked a few times in a 1 day.

Thanks.

Misunderstanding.
I mean, do people always work along the lines of this:
In: 2pm, out 3pm, in 3pm, out 4pm, in 4pm out 8pm
I.e. no gaps in between, regardless how many days they work.

Originally Posted by dip11

Misunderstanding.
I mean, do people always work along the lines of this:
In: 2pm, out 3pm, in 3pm, out 4pm, in 4pm out 8pm
I.e. no gaps in between, regardless how many days they work.

Most of the workers work once a night. All our workers work night shift. However, the supervisors go to different locations during their shift, so there hours should be consistent, such as 2 to 3 and 3 to 4 and so forth. The employees that clock in and out a few times in a shift are usually the employees that have Overlapping Punches, which is what I'm trying to find out of how to find those.

Yes, what I mean is, is it possible that there are gaps between ins and outs which you find are important to not miss.

Say if you punch out at 3pm do you punch in elsewhere at 3pm exact again or might that be 3:05?

If its exact, then do like I said and by date subtract max from min time. If there are gaps, then there's a whole lot more to it.

It is exact because we pay them for the drive to the next locaiton.

And I don't understand what do do!

Frustrating...

K-IN118 6/22/2012 BORDEN JOSHUA 6/22/2012 3:01 6/22/2012 4:49 1:48 1.8 0 1.8
K-IN500 6/22/2012 BORDEN JOSHUA 6/22/2012 0:33 6/22/2012 0:52 0:19 0.32 0 0.32
M-IN072 6/22/2012 BORDEN JOSHUA 6/22/2012 4:49 6/22/2012 6:00 1:11 1.18 0 1.18
M-IN209 6/22/2012 BORDEN JOSHUA 6/22/2012 0:52 6/22/2012 3:01 2:09 2.15 0 2.15

Get highest OUT:
6/22/2012 6:00

Get lowest IN:
6/22/2012 0:33

Subtract one from the other