TIme Clock record reformatting to ???

[Sleeplol]

Hello All,
I'd like some help formatting this (Tbl-A)(Loaded via Power Query)

into this (Tbl-B)

What I used to populate Tbl-B Columns (All the same Index/Match formula logic):
Employee Id - IFERROR(INDEX($B$2:$B$1000, SMALL(IF(ISNUMBER(SEARCH($L$1, $C$2:$C$1000)), MATCH(ROW($C$2:$C$1000), ROW($C$2:$C$1000))), ROWS($A$2:A2))),"")
Time In - IFERROR(INDEX($A$2:$A$1000, SMALL(IF(ISNUMBER(SEARCH($L$1, $C$2:$C$1000)), MATCH(ROW($C$2:$C$1000), ROW($C$2:$C$1000))), ROWS($A$2:A2))),"")
Time Out - IFERROR(INDEX($A$2:$A$1000, SMALL(IF(ISNUMBER(SEARCH($L$2, $C$2:$C$1000)), MATCH(ROW($C$2:$C$1000), ROW($C$2:$C$1000))), ROWS($A$2:A2))),"")

Problems:

• If someone fails to clock in or out, the entire column is thrown off
• The In and out times are not linked to the Employee ID so they are essentially listed in order of appearance. The red on Tbl-B indicates the start of error (Matching Tbl-A)
  

Also, the new shifts are going to be 12 hour days and 12 hour nights, and there will ALWAYS be times that folks forget to clock in or out. So is there a way to "skip" a clock out if over 15 hours from clock in time.

Please let me know the best way of going about this whether in Power Query or excel.
Many thanks for any help with this

 

[jasonb75]

Try using this formula instead, I've refreshed the query with it in the sheet and it appears to work (by adding 1/1000 sec to the previous clock in time instead of just looking for the next one).

=IF(ROWS(I$2:I2)>COUNTIF($C$2:$C$10007,$L$1),"",MINIFS($A$2:$A$10007,$A$2:$A$10007,">"&(N(I1)+0.00000001),$C$2:$C$10007,$L$1))

Note that this still doesn't show a clock out time when a clock in has been missed. Also something that I didn't think of previously, if anyone forgets to clock out, then forgets to clock in on their next shift then it will show as a single continuous shift.

As I mentioned before, I think that with formulas trying to allow for all possible scenarios would be unrealistic.

 

[Fluff]

I see what you mean, another option (which I think does the same thing is

=IF(ROWS(I$2:I2)>COUNTIF($C$2:$C$10007,$L$1),"",AGGREGATE(15,6,(ClockTime[Timezone UTC])/(ClockTime[Timezone UTC]>N(I1))/(ClockTime[No Label]=L$1),1))

 

[jasonb75]

Taking a different (but messy) approach to the task and using conditional formatting to highlight duplicates (which are caused by a missing clock in or clock out) with a yellow fill and those with a gap greater than 15 hours in a red font.

It might be possible to clean this up a bit but I'm out of ideas. I've had to split the output over 2 posts, the XL2BB code exceeds the character limit for a single one.

TimeSheet(Temps).xlsm
	A	B	C	D	E	F	G
1	Timezone UTC	Employee ID:	No Label	IP	In	Out	counter
2	8/6/20 7:02 AM	8907	Clock IN	96.65.210.85	8/6/20 7:02 AM	8/6/20 3:05 PM	1
3	8/6/20 9:21 AM	6422	Clock IN	96.65.210.85	8/6/20 9:21 AM	8/6/20 8:07 PM	1
4	8/6/20 3:05 PM	8907	Clock OUT	96.65.210.85	8/6/20 7:02 AM	8/6/20 3:05 PM	2
5	8/6/20 8:07 PM	6422	Clock OUT	96.65.210.85	8/6/20 9:21 AM	8/6/20 8:07 PM	2
6	8/7/20 7:05 AM	8907	Clock IN	96.65.210.85	8/7/20 7:05 AM	8/7/20 2:49 PM	1
7	8/7/20 2:49 PM	6422	Clock IN	96.65.210.85	8/7/20 2:49 PM	8/7/20 2:59 PM	1
8	8/7/20 2:49 PM	8907	Clock OUT	96.65.210.85	8/7/20 7:05 AM	8/7/20 2:49 PM	2
9	8/7/20 2:59 PM	6422	Clock OUT	96.65.210.86	8/7/20 2:49 PM	8/7/20 2:59 PM	2
10	8/10/20 7:09 AM	8907	Clock IN	96.65.210.85	8/10/20 7:09 AM	8/10/20 3:08 PM	1
11	8/10/20 8:24 AM	6422	Clock IN	96.65.210.85	8/10/20 8:24 AM	8/11/20 5:09 PM	1
12	8/10/20 3:08 PM	8907	Clock OUT	96.65.210.85	8/10/20 7:09 AM	8/10/20 3:08 PM	2
13	8/11/20 7:04 AM	8907	Clock IN	96.65.210.85	8/11/20 7:04 AM	8/11/20 3:09 PM	1
14	8/11/20 7:56 AM	6422	Clock IN	96.65.210.85	8/11/20 7:56 AM	8/11/20 5:09 PM	1
15	8/11/20 3:09 PM	8907	Clock OUT	96.65.210.85	8/11/20 7:04 AM	8/11/20 3:09 PM	2
16	8/11/20 5:09 PM	6422	Clock OUT	96.65.210.85	8/11/20 7:56 AM	8/11/20 5:09 PM	2
17	8/12/20 7:04 AM	8907	Clock IN	96.65.210.85	8/12/20 7:04 AM	8/12/20 3:02 PM	1
18	8/12/20 3:02 PM	8907	Clock OUT	96.65.210.85	8/12/20 7:04 AM	8/12/20 3:02 PM	2
19	8/13/20 7:05 AM	8907	Clock IN	96.65.210.85	8/13/20 7:05 AM	8/13/20 3:02 PM	1
20	8/13/20 3:02 PM	8907	Clock OUT	96.65.210.85	8/13/20 7:05 AM	8/13/20 3:02 PM	2
21	8/14/20 7:08 AM	8907	Clock IN	96.65.210.85	8/14/20 7:08 AM	8/14/20 3:00 PM	1
22	8/14/20 3:00 PM	8907	Clock OUT	96.65.210.85	8/14/20 7:08 AM	8/14/20 3:00 PM	2
23	8/17/20 7:06 AM	8907	Clock IN	96.65.210.85	8/17/20 7:06 AM	8/17/20 3:11 PM	1
24	8/17/20 3:11 PM	8907	Clock OUT	96.65.210.85	8/17/20 7:06 AM	8/17/20 3:11 PM	2
25	8/18/20 7:02 AM	8907	Clock IN	96.65.210.85	8/18/20 7:02 AM	8/18/20 3:03 PM	1
26	8/18/20 3:03 PM	8907	Clock OUT	96.65.210.85	8/18/20 7:02 AM	8/18/20 3:03 PM	2
27	8/19/20 7:02 AM	8907	Clock IN	96.65.210.85	8/19/20 7:02 AM	8/19/20 3:03 PM	1
28	8/19/20 3:03 PM	8907	Clock OUT	96.65.210.85	8/19/20 7:02 AM	8/19/20 3:03 PM	2
29	8/20/20 6:59 AM	8907	Clock IN	96.65.210.85	8/20/20 6:59 AM	8/20/20 3:03 PM	1
30	8/20/20 3:03 PM	8907	Clock OUT	96.65.210.85	8/20/20 6:59 AM	8/20/20 3:03 PM	2
31	8/21/20 7:01 AM	8907	Clock IN	96.65.210.85	8/21/20 7:01 AM	8/21/20 3:03 PM	1
32	8/21/20 3:03 PM	8907	Clock OUT	96.65.210.85	8/21/20 7:01 AM	8/21/20 3:03 PM	2
33	8/24/20 7:09 AM	8907	Clock IN	96.65.210.85	8/24/20 7:09 AM	8/24/20 3:01 PM	1
34	8/24/20 3:01 PM	8907	Clock OUT	96.65.210.85	8/24/20 7:09 AM	8/24/20 3:01 PM	2
35	8/25/20 7:07 AM	8907	Clock IN	96.65.210.85	8/25/20 7:07 AM	8/25/20 3:01 PM	1
36	8/25/20 3:01 PM	8907	Clock OUT	96.65.210.85	8/25/20 7:07 AM	8/25/20 3:01 PM	2
37	8/26/20 7:07 AM	8907	Clock IN	96.65.210.85	8/26/20 7:07 AM	8/26/20 12:55 PM	1
38	8/26/20 12:55 PM	8907	Clock OUT	96.65.210.85	8/26/20 7:07 AM	8/26/20 12:55 PM	2
39	8/27/20 7:04 AM	8907	Clock IN	96.65.210.85	8/27/20 7:04 AM	8/27/20 3:00 PM	1
40	8/27/20 3:00 PM	8907	Clock OUT	96.65.210.85	8/27/20 7:04 AM	8/27/20 3:00 PM	2
41	8/28/20 7:08 AM	8907	Clock IN	96.65.210.85	8/28/20 7:08 AM	8/28/20 3:03 PM	1
42	8/28/20 3:03 PM	8907	Clock OUT	96.65.210.85	8/28/20 7:08 AM	8/28/20 3:03 PM	2
43	8/31/20 7:05 AM	8907	Clock IN	96.65.210.85	8/31/20 7:05 AM	8/31/20 3:01 PM	1
44	8/31/20 3:01 PM	8907	Clock OUT	96.65.210.85	8/31/20 7:05 AM	8/31/20 3:01 PM	2
45	9/1/20 7:08 AM	8907	Clock IN	96.65.210.85	9/1/20 7:08 AM	9/1/20 2:59 PM	1
46	9/1/20 2:59 PM	8907	Clock OUT	96.65.210.85	9/1/20 7:08 AM	9/1/20 2:59 PM	2
47	9/2/20 7:05 AM	8907	Clock IN	96.65.210.85	9/2/20 7:05 AM	9/2/20 3:00 PM	1
48	9/2/20 3:00 PM	8907	Clock OUT	96.65.210.85	9/2/20 7:05 AM	9/2/20 3:00 PM	2
49	9/3/20 7:03 AM	8907	Clock IN	96.65.210.85	9/3/20 7:03 AM	9/3/20 3:02 PM	1
50	9/3/20 3:02 PM	8907	Clock OUT	96.65.210.85	9/3/20 7:03 AM	9/3/20 3:02 PM	2
51	9/4/20 7:09 AM	8907	Clock IN	96.65.210.85	9/4/20 7:09 AM	9/4/20 3:03 PM	1
52	9/4/20 3:03 PM	8907	Clock OUT	96.65.210.85	9/4/20 7:09 AM	9/4/20 3:03 PM	2
53	9/7/20 7:05 AM	8907	Clock IN	96.65.210.85	9/7/20 7:05 AM	9/7/20 3:01 PM	1
54	9/7/20 3:01 PM	8907	Clock OUT	96.65.210.85	9/7/20 7:05 AM	9/7/20 3:01 PM	2
55	9/8/20 7:04 AM	8907	Clock IN	96.65.210.85	9/8/20 7:04 AM	9/8/20 3:11 PM	1
56	9/8/20 3:11 PM	8907	Clock OUT	96.65.210.85	9/8/20 7:04 AM	9/8/20 3:11 PM	2
57	9/9/20 7:03 AM	8907	Clock IN	96.65.210.85	9/9/20 7:03 AM	9/9/20 3:06 PM	1
58	9/9/20 3:06 PM	8907	Clock OUT	96.65.210.85	9/9/20 7:03 AM	9/9/20 3:06 PM	2
59	9/10/20 7:07 AM	8907	Clock IN	96.65.210.85	9/10/20 7:07 AM	9/10/20 3:01 PM	1
60	9/10/20 3:01 PM	8907	Clock OUT	96.65.210.85	9/10/20 7:07 AM	9/10/20 3:01 PM	2
61	9/11/20 7:09 AM	8907	Clock IN	96.65.210.85	9/11/20 7:09 AM	9/11/20 3:01 PM	1
62	9/11/20 3:01 PM	8907	Clock OUT	96.65.210.85	9/11/20 7:09 AM	9/11/20 3:01 PM	2
63	9/14/20 7:05 AM	8907	Clock IN	96.65.210.85	9/14/20 7:05 AM	9/14/20 3:01 PM	1
64	9/14/20 3:01 PM	8907	Clock OUT	96.65.210.85	9/14/20 7:05 AM	9/14/20 3:01 PM	2
65	9/15/20 7:09 AM	8907	Clock IN	96.65.210.85	9/15/20 7:09 AM	9/15/20 3:01 PM	1
66	9/15/20 7:30 AM	8838	Clock IN	96.65.210.85	9/15/20 7:30 AM	9/15/20 4:02 PM	1
67	9/15/20 7:31 AM	7840	Clock IN	96.65.210.85	9/15/20 7:31 AM	9/15/20 4:01 PM	1
68	9/15/20 3:01 PM	8907	Clock OUT	96.65.210.85	9/15/20 7:09 AM	9/15/20 3:01 PM	2
69	9/15/20 4:01 PM	7840	Clock OUT	96.65.210.85	9/15/20 7:31 AM	9/15/20 4:01 PM	2
70	9/15/20 4:02 PM	8838	Clock OUT	96.65.210.85	9/15/20 7:30 AM	9/15/20 4:02 PM	2
71	9/16/20 6:54 AM	7840	Clock IN	96.65.210.85	9/16/20 6:54 AM		1
72	9/16/20 6:54 AM	8838	Clock IN	96.65.210.85	9/16/20 6:54 AM		1
73	9/16/20 7:03 AM	8907	Clock IN	96.65.210.85	9/16/20 7:03 AM		1
DATA

Cell Formulas
Range	Formula
E2:E73	E2	=IF([@[No Label]]=$O$1,[@[Timezone UTC]],IFERROR(1/(1/MAXIFS([Timezone UTC],[Employee ID:],[@[Employee ID:]],[No Label],$O$1,[Timezone UTC],"<"&[@[Timezone UTC]])),""))
F2:F73	F2	=IF([@[No Label]]=$O$2,[@[Timezone UTC]],IFERROR(1/(1/MINIFS([Timezone UTC],[Employee ID:],[@[Employee ID:]],[No Label],$O$2,[Timezone UTC],">"&[@[Timezone UTC]])),""))
G2:G73	G2	=COUNTIFS(B$2:B2,B2,E$2:E2,E2,F$2:F2,F2)

 

[jasonb75]

Second part, column H is empty to keep the 2 tables separate.

TimeSheet(Temps).xlsm
	I	J	K	L	M	N
1	Employee	Employee ID	TIME IN	TIME OUT		Clock IN
2	Joseph Dutch	8907	8/6/20 7:02 AM	8/6/20 3:05 PM		Clock OUT
3	Brent Wilette	6422	8/6/20 9:21 AM	8/6/20 8:07 PM
4	Joseph Dutch	8907	8/7/20 7:05 AM	8/7/20 2:49 PM
5	Brent Wilette	6422	8/7/20 2:49 PM	8/7/20 2:59 PM
6	Joseph Dutch	8907	8/10/20 7:09 AM	8/10/20 3:08 PM
7	Brent Wilette	6422	8/10/20 8:24 AM	8/11/20 5:09 PM
8	Joseph Dutch	8907	8/11/20 7:04 AM	8/11/20 3:09 PM
9	Brent Wilette	6422	8/11/20 7:56 AM	8/11/20 5:09 PM
10	Joseph Dutch	8907	8/12/20 7:04 AM	8/12/20 3:02 PM
11	Joseph Dutch	8907	8/13/20 7:05 AM	8/13/20 3:02 PM
12	Joseph Dutch	8907	8/14/20 7:08 AM	8/14/20 3:00 PM
13	Joseph Dutch	8907	8/17/20 7:06 AM	8/17/20 3:11 PM
14	Joseph Dutch	8907	8/18/20 7:02 AM	8/18/20 3:03 PM
15	Joseph Dutch	8907	8/19/20 7:02 AM	8/19/20 3:03 PM
16	Joseph Dutch	8907	8/20/20 6:59 AM	8/20/20 3:03 PM
17	Joseph Dutch	8907	8/21/20 7:01 AM	8/21/20 3:03 PM
18	Joseph Dutch	8907	8/24/20 7:09 AM	8/24/20 3:01 PM
19	Joseph Dutch	8907	8/25/20 7:07 AM	8/25/20 3:01 PM
20	Joseph Dutch	8907	8/26/20 7:07 AM	8/26/20 12:55 PM
21	Joseph Dutch	8907	8/27/20 7:04 AM	8/27/20 3:00 PM
22	Joseph Dutch	8907	8/28/20 7:08 AM	8/28/20 3:03 PM
23	Joseph Dutch	8907	8/31/20 7:05 AM	8/31/20 3:01 PM
24	Joseph Dutch	8907	9/1/20 7:08 AM	9/1/20 2:59 PM
25	Joseph Dutch	8907	9/2/20 7:05 AM	9/2/20 3:00 PM
26	Joseph Dutch	8907	9/3/20 7:03 AM	9/3/20 3:02 PM
27	Joseph Dutch	8907	9/4/20 7:09 AM	9/4/20 3:03 PM
28	Joseph Dutch	8907	9/7/20 7:05 AM	9/7/20 3:01 PM
29	Joseph Dutch	8907	9/8/20 7:04 AM	9/8/20 3:11 PM
30	Joseph Dutch	8907	9/9/20 7:03 AM	9/9/20 3:06 PM
31	Joseph Dutch	8907	9/10/20 7:07 AM	9/10/20 3:01 PM
32	Joseph Dutch	8907	9/11/20 7:09 AM	9/11/20 3:01 PM
33	Joseph Dutch	8907	9/14/20 7:05 AM	9/14/20 3:01 PM
34	Joseph Dutch	8907	9/15/20 7:09 AM	9/15/20 3:01 PM
35	Calvin Lane	8838	9/15/20 7:30 AM	9/15/20 4:02 PM
36	Mick Franklin	7840	9/15/20 7:31 AM	9/15/20 4:01 PM
37	Mick Franklin	7840	9/16/20 6:54 AM
38	Calvin Lane	8838	9/16/20 6:54 AM
39	Joseph Dutch	8907	9/16/20 7:03 AM
40
DATA

Cell Formulas
Range	Formula
I2:I40	I2	=IFERROR(VLOOKUP([@[Employee ID]],Temps!E:F,2,FALSE),"")
J2:J40	J2	=IFERROR(INDEX(ClockTime[Employee ID:],AGGREGATE(15,6,ROW(ClockTime[counter])/(ClockTime[counter]=1),ROWS(J$2:J2))-1),"")
K2:K40	K2	=IFERROR(INDEX(ClockTime[In],AGGREGATE(15,6,ROW(ClockTime[counter])/(ClockTime[counter]=1),ROWS(J$2:J2))-1),"")
L2:L40	L2	=IFERROR(INDEX(ClockTime[Out],AGGREGATE(15,6,ROW(ClockTime[counter])/(ClockTime[counter]=1),ROWS(J$2:J2))-1),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K2:L40	Cell Value	duplicates	text	NO
K2:L40	Expression	=($L2-$K2)>TIME(15,0,0)	text	NO

 

[Sleeplol]

Sorry for getting back so late with this reply

Hi Fluff,
Thanks for your formula, yes it works great.

Hi Jason,
Man, I really appreciate all you effort with this. I've incorporated your last post which makes it super easy to see IN and OUT 'errors'. Using your update I'm putting in an "If" function column so that when they run reports (Script uses Advanced Filter) they'll see "Verify Time".