Time Sheet?

[oogles36]

I have a list of Clock in and Clock outs. Its formatted like this:

Column A : Employee Name
Column E: Date and time in this format: May 16, 2023 10:21:14 PM
Column F: On (for an on punch or Off for a clock out)

The list I have is about 150k rows....

I'm trying to find a formula or a way to just see employees that clock in with less than 10 hours off.

So if you clocked out May 16, 2023 at 10:21:14 PM and then clock in May 17, 2023 at 01:14:14 AM you would be flagged somehow...

 

[jorismoerings]

Perhaps something like this. The FALSE lines indicate a clock on date/time in shorter period than 10 Hrs.

Book1
	A	B	C	D	E	F	G
1	name				date	on/off
2	Emp 1				16-05-23 10:21 PM	off
3	Emp 1				17-05-23 1:14 AM	on	FALSE
4	Emp 3				18-11-23 8:00 AM	on	TRUE
5	Emp 4				18-11-23 8:00 AM	on	TRUE
6	Emp 5				18-11-23 8:00 AM	on	TRUE
7	Emp 6				18-11-23 8:00 AM	on	TRUE
8	Emp 7				18-11-23 8:00 AM	on	TRUE
9	Emp 8				18-11-23 8:00 AM	on	TRUE
10	Emp 9				18-11-23 8:00 AM	on	TRUE
11	Emp 1				18-11-23 5:00 PM	off
12	Emp 2				18-11-23 5:00 PM	off
13	Emp 3				18-11-23 5:00 PM	off
14	Emp 4				18-11-23 11:00 PM	off
15	Emp 5				18-11-23 5:00 PM	off
16	Emp 6				18-11-23 5:00 PM	off
17	Emp 7				18-11-23 5:00 PM	off
18	Emp 1				19-11-23 8:00 AM	on	TRUE
19	Emp 2				19-11-23 8:00 AM	on	TRUE
20	Emp 3				19-11-23 8:00 AM	on	TRUE
21	Emp 4				19-11-23 8:00 AM	on	FALSE
22	Emp 5				19-11-23 8:00 AM	on	TRUE
23	Emp 6				19-11-23 8:00 AM	on	TRUE
24	Emp 7				19-11-23 8:00 AM	on	TRUE
Sheet1

Cell Formulas
Range	Formula
G2:G24	G2	=IF(F2="on",(E2-MAXIFS(E$1:E1,F$1:F1,"off",A$1:A1,A2))>TIME(10,0,0),"")

 

[oogles36]

So far so good, how do you get it to say true or false? mine is coming up #NAME? instead of False

 

[oogles36]

Ah I think its because mine started at E3 lol I updated the formula and its running it now, Maybe I shouldn't have had it do 70k rows at once but we will see how long it takes.

 

[jorismoerings]

So far so good, how do you get it to say true or false? mine is coming up #NAME? instead of False

Hi, that's my bad. MAXIFS isn't available on O2016. I'll figure out an alternative.

 

[jorismoerings]

See the O2016 column for an alternative:

Book1
	A	B	C	D	E	F	G	H
1	name				date	on/off	O365	O2016
2	Emp 1				16-05-23 10:21 PM	off
3	Emp 1				17-05-23 1:14 AM	on	FALSE	FALSE
4	Emp 3				18-11-23 8:00 AM	on	TRUE	TRUE
5	Emp 4				18-11-23 8:00 AM	on	TRUE	TRUE
6	Emp 5				18-11-23 8:00 AM	on	TRUE	TRUE
7	Emp 6				18-11-23 8:00 AM	on	TRUE	TRUE
8	Emp 7				18-11-23 8:00 AM	on	TRUE	TRUE
9	Emp 8				18-11-23 8:00 AM	on	TRUE	TRUE
10	Emp 9				18-11-23 8:00 AM	on	TRUE	TRUE
11	Emp 1				18-11-23 5:00 PM	off
12	Emp 2				18-11-23 5:00 PM	off
13	Emp 3				18-11-23 5:00 PM	off
14	Emp 4				18-11-23 11:00 PM	off
15	Emp 5				18-11-23 5:00 PM	off
16	Emp 6				18-11-23 5:00 PM	off
17	Emp 7				18-11-23 5:00 PM	off
18	Emp 1				19-11-23 8:00 AM	on	TRUE	TRUE
19	Emp 2				19-11-23 8:00 AM	on	TRUE	TRUE
20	Emp 3				19-11-23 8:00 AM	on	TRUE	TRUE
21	Emp 4				19-11-23 8:00 AM	on	FALSE	FALSE
22	Emp 5				19-11-23 8:00 AM	on	TRUE	TRUE
23	Emp 6				19-11-23 8:00 AM	on	TRUE	TRUE
24	Emp 7				19-11-23 8:00 AM	on	TRUE	TRUE
Sheet1

Cell Formulas
Range	Formula
G2:G24	G2	=IF(F2="on",(E2-MAXIFS(E$1:E1,F$1:F1,"off",A$1:A1,A2))>TIME(10,0,0),"")
H2:H24	H2	=IF(F2="on",(E2-MAX(IF(F$1:F1="off",IF(A$1:A1=A2,E$1:E1,""),"")))>TIME(10,0,0),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[oogles36]

ah thank you, trying now

 

[oogles36]

Book1.xlsx
	A	B	C	D	E	F	G
1	JOHN DOE				Sep 6, 2023 4:47:03 AM	on
2					Sep 6, 2023 1:16:51 PM	off	TRUE
3					Sep 7, 2023 4:49:33 AM	on
4					Sep 7, 2023 6:36:54 PM	off	TRUE
5					Sep 8, 2023 4:48:53 AM	on
6					Sep 8, 2023 2:41:28 PM	off	TRUE
7					Sep 10, 2023 5:13:39 AM	on
8					Sep 10, 2023 2:42:03 PM	OFF	TRUE
9					Sep 11, 2023 4:49:56 AM	on
10					Sep 11, 2023 2:42:53 PM	off	TRUE
11					Sep 12, 2023 4:47:21 AM	on
12					Sep 12, 2023 2:39:46 PM	off	TRUE
13					Sep 13, 2023 4:50:34 AM	on
14					Sep 13, 2023 2:46:19 PM	off	TRUE
15					Sep 14, 2023 4:43:23 AM	on
16					Sep 14, 2023 1:03:23 PM	off	TRUE
17					Sep 15, 2023 4:49:58 AM	on
18					Sep 15, 2023 2:38:02 PM	off	TRUE
19					Sep 18, 2023 4:45:32 AM	on
20					Sep 18, 2023 2:52:49 PM	off	TRUE
21					Sep 19, 2023 4:42:18 AM	on
22					Sep 19, 2023 3:00:53 PM	off	TRUE
23					Sep 20, 2023 4:48:59 AM	on
24					Sep 20, 2023 2:53:44 PM	off	TRUE
25					Sep 21, 2023 4:51:14 AM	on
26					Sep 21, 2023 2:42:11 PM	off	TRUE
27					Sep 22, 2023 4:50:25 AM	on
28					Sep 22, 2023 2:41:58 PM	off	TRUE
29					Sep 24, 2023 5:10:11 AM	on
30					Sep 24, 2023 7:12:56 PM	off	FALSE
31					Sep 25, 2023 4:42:09 AM	on
32					Sep 25, 2023 2:37:39 PM	off	TRUE
33					Sep 26, 2023 4:47:01 AM	on
34					Sep 26, 2023 1:01:51 PM	off	TRUE
35					Sep 27, 2023 4:48:31 AM	on
36					Sep 27, 2023 2:45:20 PM	off	TRUE
37					Sep 28, 2023 4:26:50 AM	on
38					Sep 28, 2023 8:07:07 PM	off	FALSE
39					Sep 29, 2023 4:38:11 AM	on
40					Sep 29, 2023 12:29:17 PM	off
Sheet1

Cell Formulas
Range	Formula
G1:G40	G1	=IF(F2="on",(E2-MAX(IF(F$1:F1="off",IF(A$1:A1=A2,E$1:E1,""),"")))>TIME(10,0,0),"")

 

[oogles36]

Report September 2023.xlsx
	A				E	F	G
1	NAME 1				Sep 6, 2023 4:47:03 AM	on
2					Sep 6, 2023 11:16:51 PM	off	FALSE
3					Sep 7, 2023 4:49:33 AM	on
4					Sep 7, 2023 6:36:54 PM	off	TRUE
5					Sep 8, 2023 4:48:53 AM	on
6					Sep 8, 2023 2:41:28 PM	off	TRUE
7					Sep 10, 2023 5:13:39 AM	on
8					Sep 10, 2023 2:42:03 PM	off	TRUE
9					Sep 11, 2023 4:49:56 AM	on
10					Sep 11, 2023 2:42:53 PM	off	TRUE
11					Sep 12, 2023 4:47:21 AM	on
12					Sep 12, 2023 2:39:46 PM	off	TRUE
13					Sep 13, 2023 4:50:34 AM	on
14					Sep 13, 2023 2:46:19 PM	off	TRUE
15					Sep 14, 2023 4:43:23 AM	on
16					Sep 14, 2023 1:03:23 PM	off	TRUE
17					Sep 15, 2023 4:49:58 AM	on
18					Sep 15, 2023 2:38:02 PM	off	TRUE
19					Sep 18, 2023 4:45:32 AM	on
20					Sep 18, 2023 2:52:49 PM	off	TRUE
21					Sep 19, 2023 4:42:18 AM	on
22					Sep 19, 2023 3:00:53 PM	off	TRUE
23					Sep 20, 2023 4:48:59 AM	on
24					Sep 20, 2023 2:53:44 PM	off	TRUE
25					Sep 21, 2023 4:51:14 AM	on
26					Sep 21, 2023 2:42:11 PM	off	TRUE
27					Sep 22, 2023 4:50:25 AM	on
28					Sep 22, 2023 2:41:58 PM	off	TRUE
29					Sep 24, 2023 5:10:11 AM	on
30					Sep 24, 2023 7:12:56 PM	off	TRUE
31					Sep 25, 2023 4:42:09 AM	on
32					Sep 25, 2023 2:37:39 PM	off	TRUE
33					Sep 26, 2023 4:47:01 AM	on
34					Sep 26, 2023 1:01:51 PM	off	TRUE
35					Sep 27, 2023 4:48:31 AM	on
36					Sep 27, 2023 2:45:20 PM	off	TRUE
37					Sep 28, 2023 4:26:50 AM	on
38					Sep 28, 2023 8:07:07 PM	off	TRUE
39					Sep 29, 2023 4:38:11 AM	on
40					Sep 29, 2023 12:29:17 PM	off	#VALUE!
41	NAME 2				Sep 5, 2023 8:59:40 AM	on
42					Sep 5, 2023 5:00:05 PM	off	FALSE
43					Sep 6, 2023 9:00:43 AM	ON
44					Sep 6, 2023 5:00:55 PM	off	FALSE
45					Sep 7, 2023 8:58:55 AM	on
46					Sep 7, 2023 5:00:13 PM	off	FALSE
47					Sep 8, 2023 8:53:16 AM	on
48					Sep 8, 2023 5:00:20 PM	off	FALSE
49					Sep 11, 2023 6:39:59 AM	on	#VALUE!
Page1_1

Cell Formulas
Range	Formula
G1:G49	G1	=IF(F2="on",(E2-MAX(IF(F$1:F1="off",IF(A$1:A1=A2,E$1:E1,""),"")))>TIME(8,0,0),"")

 

[oogles36]

Not too sure why it does the Value after the employee name changes? Or why it switches from true/false?

Sorry lol