Timesheet check in time tracker

[muhleebbin]

I'm trying to automate the process of finding hourly employees discrepancy between time inputted in the timesheets vs. a clock in system that we have going.

The software spits out ugly data from our clock in system and was wondering if there's a way to simplify the data to give me the start times for each day and each employee. Below is an example of data

TimeTrax 11.23.20.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M
95	TIME CARD REPORT		Employee Name:	Test 1								Page 11 of	13
96	Payroll Group: Shop / Employee Group: Shop - Default Emp Grp
97	For The Pay Period: 11/23/2020 12:00 AM Thru 11/30/2020 12:00 AM
98													Printed: 02-Dec-20 08:23 AM
99	Status	Actual	Edited			In/Out	Reg		Ot1		Ot2		Total
100		Tue Nov 24 07:00 AM	Tue Nov 24 07:00 AM			In
101	Rule					Missed Out
102		Wed Nov 25 07:01 AM	Wed Nov 25 07:01 AM			In
103		Wed Nov 25 12:01 PM	Wed Nov 25 12:01 PM			Out	5:00						5:00
104		Wed Nov 25 12:31 PM	Wed Nov 25 12:31 PM			In
105	Rule					Missed Out
106						Total	5:00		0:00		0:00		5:00
107	TIME CARD REPORT		Employee Name:	Test 2								Page 12 of	13
108	Payroll Group: Shop / Employee Group: Shop - Default Emp Grp
109	For The Pay Period: 11/23/2020 12:00 AM Thru 11/30/2020 12:00 AM
110													Printed: 02-Dec-20 08:23 AM
111	Status	Actual	Edited			In/Out	Reg		Ot1		Ot2		Total
112		Mon Nov 23 07:18 AM	Mon Nov 23 07:18 AM			In
113		Mon Nov 23 12:01 PM	Mon Nov 23 12:01 PM			Out	4:43
114		Mon Nov 23 12:31 PM	Mon Nov 23 12:31 PM			In
115		Mon Nov 23 03:30 PM	Mon Nov 23 03:30 PM			Out	2:59						7:42
116		Tue Nov 24 07:00 AM	Tue Nov 24 07:00 AM			In
117		Tue Nov 24 12:00 PM	Tue Nov 24 12:00 PM			Out	5:00
118		Tue Nov 24 12:30 PM	Tue Nov 24 12:30 PM			In
119		Tue Nov 24 03:32 PM	Tue Nov 24 03:32 PM			Out	3:02						8:02
120		Wed Nov 25 07:02 AM	Wed Nov 25 07:02 AM			In
121		Wed Nov 25 12:00 PM	Wed Nov 25 12:00 PM			Out	4:58
122		Wed Nov 25 12:30 PM	Wed Nov 25 12:30 PM			In
123		Wed Nov 25 01:31 PM	Wed Nov 25 01:31 PM			Out	1:01						5:59
124						Total	21:43		0:00		0:00		21:43
Sheet1

If this is possible, I was then hoping to be able to create a secondary code that would then use that information and find if there's anyone that has a discrepancy of 10 minutes or more then it would change the font to red so that we can accurately pay our hourly employees. In the sample below Test 2 employee's 11/23/20 start time should be 7:18 clock in time vs the 7:00 inputted time.

SUBMITTED TIME REPORT -- 11232020 - Mon - 11292020 - Sun.xlsm
	A	B		D	E	F		H	I								Q	R	S	T
56	39 - Test 2																		Subtotal Hours : 38	39
57	11/23/2020	Test		7:00 AM	12:00 PM	310 - Countertop - Fabrication		5	REG								109 - Catering Kitchen	Pending	12/01/2020 9:08 pm UTC	39.1
58	11/23/2020	Test		12:30 PM	3:30 PM	310 - Countertop - Fabrication		3	REG								109 - Catering Kitchen	Pending	12/01/2020 9:09 pm UTC	39.1
59	11/24/2020	Test		7:00 AM	11:00 AM	200 - Steel Vessel Fabrication		4	REG								102 - Lobby Water Feature 1	Pending	12/01/2020 9:11 pm UTC	39.1
60	11/24/2020	Test		11:00 AM	12:00 PM	310 - Countertop - Fabrication		1	REG								101 - Countertop	Pending	12/01/2020 9:12 pm UTC	39.1
61	11/24/2020	Test		12:30 PM	3:30 PM	310 - Countertop - Fabrication		3	REG								101 - Countertop	Pending	12/01/2020 9:13 pm UTC	39.1
62	11/25/2020	Test		7:00 AM	12:00 PM	210 - Backsplash - Fabrication		5	REG								109 - Catering Kitchen	Pending	12/01/2020 9:52 pm UTC	39.1
63	11/25/2020	Test		12:30 PM	1:30 PM	20 - Shop and Equipment Maintenance		1	REG								1 - NO PHASE - Samples	Pending	12/01/2020 9:53 pm UTC	39.1
64	11/26/2020	Test		7:00 AM	3:00 PM	1450 - LMT - Holiday		8	REG								1 - NO PHASE - Samples	Pending	12/01/2020 9:55 pm UTC	39.1
65	11/27/2020	Test		7:00 AM	3:00 PM	1400 - LMT - PTO		8	REG								1 - NO PHASE - Samples	Pending	12/01/2020 9:55 pm UTC	39.1
66								38
67								0
Sheet1

Cell Formulas
Range	Formula
H66	H66	=SUM($H$57:$H$65)
H67	H67	=IF(H66>40,IF((H66-SUMIF($F$57:$F$65,"*PTO*",$H$57:$H$65)-SUMIF($F$57:$F$65,"*Holiday*",$H$57:$H$65)>40),H66-SUMIF($F$57:$F$65,"*PTO*",$H$57:$H$65)-SUMIF($F$57:$F$65,"*Holiday*",$H$57:$H$65)-40,0),0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2:E474,E476:E621	Cell Value	contains "1 - General Costs"	text	NO

Thank you in advance for your assistance