Improving my formulas/approach for date/time analysis

[mediumrare]

I've cobbled together a spreadsheet to help the company analyze any individual's hours. The software we use is simple but will export data in xml.

Since the data is imported as text, it needs to be transformed into usable data. Thus, I've created some patchwork here and there to get what I've needed. What I would like to do is circle back to the formulas and learn if there are simpler or more effective ways of arriving at the data I'm after for analysis.

Here is a sample of the raw data:

hours analysis deID.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Payroll Detail
2	For the period of 1/1/2021 to 9/5/2021
3				Date	Time	Date	Time
4	Week		Day	In	In	Out	Out	Job Code	Break	Hours	Reg	Ovt1	Day Total
5
6
7	1	[	Fri	1/1	08:00 AM	---	---	11-Holiday		8:00	8:00	0:00	8:00
8										8:00	8:00	0:00	8:00
9
10	2	[	Mon	1/11	06:29 AM	1/11	04:28 PM	1-Labor		9:59	9:59	0:00	9:59
11										9:59	9:59	0:00	9:59
12
13	3	[	Tue	1/12	06:28 AM	1/12	04:28 PM	1-Labor		10:00	10:00	0:00	10:00
14		[	Wed	1/13	06:00 AM	1/13	04:25 PM	1-Labor		10:25	10:25	0:00	10:25
15		[	Thu	1/14	06:28 AM	1/14	04:30 PM	1-Labor		10:02	10:02	0:00	10:02
16		[	Fri	1/15	06:27 AM	1/15	03:30 PM	1-Labor		9:03	9:03	0:00	9:03
17		[	Mon	1/18	06:27 AM	1/18	04:22 PM	1-Labor		9:55	0:30	9:25	9:55
18										49:25	40:00	9:25	49:25
19
20	4	[	Tue	1/19	06:31 AM	1/19	04:28 PM	1-Labor		9:57	9:57	0:00	9:57
21		[	Wed	1/20	06:27 AM	1/20	04:26 PM	1-Labor		9:59	9:59	0:00	9:59
22		[	Thu	1/21	07:16 AM	1/21	04:45 PM	1-Labor		9:29	9:29	0:00	9:29
23		[	Fri	1/22	06:27 AM	1/22	04:27 PM	1-Labor		10:00	10:00	0:00	10:00
24		[	Mon	1/25	07:09 AM	1/25	04:30 PM	1-Labor		9:21	0:35	8:46	9:21
25										48:46	40:00	8:46	48:46
26
27	5	[	Wed	1/27	06:27 AM	1/27	04:25 PM	1-Labor		9:58	9:58	0:00	9:58
28		[	Thu	1/28	06:29 AM	1/28	04:24 PM	1-Labor		9:55	9:55	0:00	9:55
29		[	Fri	1/29	06:30 AM	1/29	04:27 PM	1-Labor		9:57	9:57	0:00	9:57
30										29:50	29:50	0:00	29:50
Individual Analysis (2)

And here is a sample of how I currently transform it.

hours analysis deID.xlsx
	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
1										Instructions
2										1. copy XML of individual's hours to be analyzed
3										2. make sure you adjust the ranges in your formulas (see red tabs for notes) (datein, timemin, timein, daysworked, jobcode)
4	Collecting Information for Analysis									3. you can edit the named ranges: Formulas > Name Manager
5										4. make sure you remember to empty all non-date cells in Column O, otherwise formulas will give you #VALUE errors
6	Date In	Date Out	Time (Min)	Time In		Year 2021	#	Day
7	01/01/21		480	08:00 AM		01/01/21	5	Fri					0:00	8:00	10:59
8			480			01/02/21	6	Sat					1	2	3	4	5	6	7	#
9						01/03/21	7	Sun		Average Time In	7:01 AM		Mon	Tue	Wed	Thu	Fri	Sat	Sun	Day of the Week
10	01/11/21	01/11/21	599	06:29 AM		01/04/21	1	Mon					35	35	35	35	36			Days in Period
11			599			01/05/21	2	Tue		Job Codes (count)			29	28	29	31	30	2	0	Days Worked in Period
12						01/06/21	3	Wed		11-Holiday	3		6	7	6	4	6			Difference
13	01/12/21	01/12/21	600	06:28 AM		01/07/21	4	Thu		1-Labor	145
14	01/13/21	01/13/21	625	06:00 AM		01/08/21	5	Fri		2-Vacation Leave	5								147/176	weekday shifts worked
15	01/14/21	01/14/21	602	06:28 AM		01/09/21	6	Sat											29	weekday shifts missed
16	01/15/21	01/15/21	543	06:27 AM		01/10/21	7	Sun		non-labor codes			early clock-in detail			late clock-in detail
17	01/18/21	01/18/21	595	06:27 AM		01/11/21	1	Mon		11-Holiday	Jan 01		8:00 AM	x		12:00 PM	x
18			2965			01/12/21	2	Tue			May 31		7:50 AM	1		11:00 AM	1
19						01/13/21	3	Wed			Jul 05		7:40 AM	2		10:00 AM	0
20	01/19/21	01/19/21	597	06:31 AM		01/14/21	4	Thu		2-Vacation Leave	Aug 02		7:30 AM	3		9:00 AM	4
21	01/20/21	01/20/21	599	06:27 AM		01/15/21	5	Fri			Aug 03		7:20 AM	2		8:30 AM	2
22	01/21/21	01/21/21	569	07:16 AM		01/16/21	6	Sat			Aug 04		7:10 AM	6		8:15 AM	0
23	01/22/21	01/22/21	600	06:27 AM		01/17/21	7	Sun			Aug 05		7:00 AM	5		8:05 AM	1
24	01/25/21	01/25/21	561	07:09 AM		01/18/21	1	Mon			Aug 06		<7:00 AM	112		8:00 AM	10
25			2926			01/19/21	2	Tue
26						01/20/21	3	Wed		# of shifts less than 6 hr	7				missed & short weekday shifts
27	01/27/21	01/27/21	598	06:27 AM		01/21/21	4	Thu		enter hr amt here	6				date	day	details
28	01/28/21	01/28/21	595	06:29 AM		01/22/21	5	Fri		date	min	hh:mm	day		01/04/21	Mon	missed
29	01/29/21	01/29/21	597	06:30 AM		01/23/21	6	Sat		Sat. Feb 20	180	03:00	Sat		01/05/21	Tue	missed
30			1790			01/24/21	7	Sun		Fri. Apr 02	336	05:36	Fri		01/06/21	Wed	missed
31						01/25/21	1	Mon		Thu. Apr 22	310	05:10	Thu		01/07/21	Thu	missed
32	02/02/21	02/02/21	603	06:27 AM		01/26/21	2	Tue		Sat. May 29	180	03:00	Sat		01/08/21	Fri	missed
33	02/03/21	02/03/21	598	06:28 AM		01/27/21	3	Wed		Fri. Jun 18	228	03:48	Fri		01/26/21	Tue	missed
34	02/04/21	02/04/21	600	06:26 AM		01/28/21	4	Thu		Mon. Jun 28	238	03:58	Mon		02/01/21	Mon	missed
35	02/05/21	02/05/21	601	06:25 AM		01/29/21	5	Fri		Thu. Jul 15	300	05:00	Thu		02/15/21	Mon	missed
36	02/08/21	02/08/21	601	06:29 AM		01/30/21	6	Sat							02/16/21	Tue	missed
Individual Analysis (2)

Cell Formulas
Range	Formula
O7,O32:O36,O27:O29,O20:O24,O13:O17,O10	O7	=IF(ISBLANK(D7),"",DATEVALUE(D7))
P7,P32:P36,P27:P29,P20:P24,P13:P17,P10	P7	=IFERROR((DATEVALUE(F7)),"")
Q7:Q36	Q7	=IFERROR(1/(1/((TEXT(M7,"[hh]:mm")*1440))),"")
R7:R36	R7	=IFERROR(1/(1/(E7)),"")
U7:U36	U7	=WEEKDAY(T7,2)
V7:V36	V7	=TEXT(T7,"ddd")
Y9	Y9	=AVERAGE(timein)
AA10:AE10	AA10	=SUMPRODUCT((WEEKDAY(daysworked,2)=AA8)*(daysworked<>""))
AA11:AG11	AA11	=SUMPRODUCT((WEEKDAY(datein,2)=AA8)*(datein<>"")/COUNTIF(datein,datein&""))
X12:X14	X12	=UNIQUE(FILTER(jobcode,jobcode<>""))
AA12:AE12	AA12	=AA10-AA11
Y12:Y14	Y12	=COUNTIF(jobcode,X12)
AG14	AG14	=CONCATENATE(SUM(AA11:AE11),"/",SUM(AA10:AE10))
AG15	AG15	=SUM(AA12:AE12)
X17	X17	=X12
Y17:Y19	Y17	=DATEVALUE(FILTER(D7:D227,jobcode=X17))
X20	X20	=X14
Y20:Y24	Y20	=DATEVALUE(FILTER(D7:D227,jobcode=X20))
AB18	AB18	=COUNTIFS(R7:R227,">="&$AA18,R7:R227,"<"&AA17)
AB19	AB19	=COUNTIFS(R7:R227,">="&AA19,R7:R227,"<"&AA18)
AB20	AB20	=COUNTIFS(R7:R227,">="&AA20,R7:R227,"<"&AA19)
AB21	AB21	=COUNTIFS(R7:R227,">="&AA21,R7:R227,"<"&AA20)
AB22	AB22	=COUNTIFS(R7:R227,">="&AA22,R7:R227,"<"&AA21)
AB23	AB23	=COUNTIFS(R7:R227,">="&AA23,R7:R227,"<"&AA22)
AB24	AB24	=COUNTIF(R7:R227,"<"&AA23)
AE18	AE18	=COUNTIFS(R7:R227,">="&AD18,R7:R227,"<"&AD17)
AE19	AE19	=COUNTIFS(R7:R227,">="&AD19,R7:R227,"<"&AD18)
AE20	AE20	=COUNTIFS(R7:R227,">="&AD20,R7:R227,"<"&AD19)
AE21	AE21	=COUNTIFS(R7:R227,">="&AD21,R7:R227,"<"&AD20)
AE22	AE22	=COUNTIFS(R7:R227,">="&AD13,R7:R227,"<"&AD21)
AE23	AE23	=COUNTIFS(R7:R227,">="&AD23,R7:R227,"<"&AD22)
AE24	AE24	=COUNTIFS(R7:R227,">="&AD24,R7:R227,"<"&AD23)
X26	X26	=CONCATENATE("# of shifts less than"," ",Y27," hr")
Y26	Y26	=COUNT(Y29:Y371)
X29:Y35	X29	=UNIQUE(FILTER(P7:Q200,Q7:Q200<(Y27*60)))
Z29:Z34	Z29	=Y29/1440
AA29:AA34	AA29	=TEXT(X29,"ddd")
Z35:Z36	Z35	=IF(ISBLANK(Y35),"",Y35/1440)
AA35:AA36	AA35	=IF(ISBLANK(Y35),"",TEXT(X35,"ddd"))
AD28:AD36	AD28	=TEXT(AC28,"ddd")
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
'Individual Analysis (2)'!datein	='Individual Analysis (2)'!$O$7:$O$371	AA11:AG11
'Individual Analysis (2)'!daysworked	='Individual Analysis (2)'!$T$7:$T$252	AA10:AE10, U7:V7
'Individual Analysis (2)'!jobcode	='Individual Analysis (2)'!$H$7:$H$227	Y20, Y17, X12, Y12:Y14
'Individual Analysis (2)'!timein	='Individual Analysis (2)'!$R$7:$R$371	AB18:AB24, AE18:AE24, Y9
'Individual Analysis (2)'!timemin	='Individual Analysis (2)'!$Q$7:$Q$371	X29
'Individual Analysis (2)'!Year2021	='Individual Analysis (2)'!$T$7:$T$371	AA10:AE10, U7:V7

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
T7:T371	Expression	=MATCH(T7,datein,0)	text	NO
Q7:S371	Cell Value	between 1 and 240	text	NO
U7:V371	Expression	=WEEKDAY($U7,1)<=5	text	NO
O7:P7,O9:P371,P8	Expression	=$O7<>$P7	text	NO
P1	Expression	=$P1<>$P8	text	NO

Some of what I've done so far is not entirely necessary but was/is visually helpful. What's listed under "Shift Analysis" is what I'm really after.

The items in red are the ones that I'm most curious about finding ways to simplify or improve. Where there is no data in Columns O,P (i.e., when there's no date/time), it creates #VALUE errors in AA-AH (understandably despite attempts with IF and IFERROR). Further, since the period under review can change, it also means that the ranges and named ranges have to be updated each time. While not the end of the world, it does require more time.

Any suggestions?


Shift Analysis
Average Time In =AVERAGE(range)
Job Codes (count) =COUNTIF(range,reference)
List all non-labor job codes (e.g., holidays, leave) =FILTER(range,range=reference)
List all missed weekday shifts
List all weekday shifts under # hr =FILTER(range,range=reference)
Count all possible weekday shifts
Count all days worked
Count all missed possible weekday shifts

Columns, Formulas
Year 2021 and Day are currently used as named ranges to collect data on days worked and possible days worked. The # column (U) is used as a helper to adjust the weekday to Jan 1 2021.

Current formulas used for counting days
=SUMPRODUCT((WEEKDAY(daysworked,2)=AA8)*(daysworked<>""))
=SUMPRODUCT((WEEKDAY(datein,2)=AA8)*(datein<>"")/COUNTIF(datein,datein&""))

Date In =IF(ISBLANK(D7),"",DATEVALUE(D7))
Date Out =IFERROR((DATEVALUE(F7)),"")
Time (minutes)
Time In
daysworked = days of the week (Day column)
datein = the date they clocked in
AA8 = 1 (as first day of week, Monday)

Conditional Formatting
If “Date In” and “Date Out” do not match =$O7<>$P7
If “Day” is a weekday =WEEKDAY($U7,1)<=5
If the day in “Year 2021” has any hours matched to it =MATCH(T7,datein,0)

 

[earthworm]

Trust me you are working in a very messy way . Give small data for easy understanding

Organize all your core logic in one place

 

[mediumrare]

I appreciate your honesty. I enjoy Excel and want to learn more.

Let me know if this is a sufficient data size.

hours analysis deID.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Payroll Detail
2	For the period of 1/1/2021 to 9/5/2021
3				Date	Time	Date	Time
4	Week		Day	In	In	Out	Out	Job Code	Break	Hours	Reg	Ovt1	Day Total
5
6
7	1	[	Fri	1/1	08:00 AM	---	---	11-Holiday		8:00	8:00	0:00	8:00
8										8:00	8:00	0:00	8:00
9
10	2	[	Mon	1/11	06:29 AM	1/11	04:28 PM	1-Labor		9:59	9:59	0:00	9:59
11										9:59	9:59	0:00	9:59
12
13	3	[	Tue	1/12	06:28 AM	1/12	04:28 PM	1-Labor		10:00	10:00	0:00	10:00
14		[	Wed	1/13	06:00 AM	1/13	04:25 PM	1-Labor		10:25	10:25	0:00	10:25
15		[	Thu	1/14	06:28 AM	1/14	04:30 PM	1-Labor		10:02	10:02	0:00	10:02
16		[	Fri	1/15	06:27 AM	1/15	03:30 PM	1-Labor		9:03	9:03	0:00	9:03
17		[	Mon	1/18	06:27 AM	1/18	04:22 PM	1-Labor		9:55	0:30	9:25	9:55
18										49:25	40:00	9:25	49:25
19
20	4	[	Tue	1/19	06:31 AM	1/19	04:28 PM	1-Labor		9:57	9:57	0:00	9:57
21		[	Wed	1/20	06:27 AM	1/20	04:26 PM	1-Labor		9:59	9:59	0:00	9:59
22		[	Thu	1/21	07:16 AM	1/21	04:45 PM	1-Labor		9:29	9:29	0:00	9:29
23		[	Fri	1/22	06:27 AM	1/22	04:27 PM	1-Labor		10:00	10:00	0:00	10:00
24		[	Mon	1/25	07:09 AM	1/25	04:30 PM	1-Labor		9:21	0:35	8:46	9:21
25										48:46	40:00	8:46	48:46
26
27	5	[	Wed	1/27	06:27 AM	1/27	04:25 PM	1-Labor		9:58	9:58	0:00	9:58
28		[	Thu	1/28	06:29 AM	1/28	04:24 PM	1-Labor		9:55	9:55	0:00	9:55
29		[	Fri	1/29	06:30 AM	1/29	04:27 PM	1-Labor		9:57	9:57	0:00	9:57
30										29:50	29:50	0:00	29:50
31
32	6	[	Tue	2/2	06:27 AM	2/2	04:30 PM	1-Labor		10:03	10:03	0:00	10:03
33		[	Wed	2/3	06:28 AM	2/3	04:26 PM	1-Labor		9:58	9:58	0:00	9:58
34		[	Thu	2/4	06:26 AM	2/4	04:26 PM	1-Labor		10:00	10:00	0:00	10:00
35		[	Fri	2/5	06:25 AM	2/5	04:26 PM	1-Labor		10:01	9:59	0:02	10:01
36		[	Mon	2/8	06:29 AM	2/8	04:30 PM	1-Labor		10:01	0:00	10:01	10:01
37										50:03	40:00	10:03	50:03
38
39	7	[	Tue	2/9	06:15 AM	2/9	04:30 PM	1-Labor		10:15	10:15	0:00	10:15
40		[	Wed	2/10	06:25 AM	2/10	04:25 PM	1-Labor		10:00	10:00	0:00	10:00
41		[	Thu	2/11	06:20 AM	2/11	04:20 PM	1-Labor		10:00	10:00	0:00	10:00
42		[	Fri	2/12	06:25 AM	2/12	04:30 PM	1-Labor		10:05	9:45	0:20	10:05
43										40:20	40:00	0:20	40:20
44
45	8	[	Thu	2/18	06:50 AM	2/18	04:00 PM	1-Labor		9:10	9:10	0:00	9:10
46		[	Fri	2/19	07:40 AM	2/19	04:30 PM	1-Labor		8:50	8:50	0:00	8:50
47		[	Sat	2/20	07:30 AM	2/20	10:30 AM	1-Labor		3:00	3:00	0:00	3:00
48		[	Mon	2/22	07:30 AM	2/22	04:30 PM	1-Labor		9:00	9:00	0:00	9:00
49										30:00	30:00	0:00	30:00
50
51	9	[	Tue	2/23	06:26 AM	2/23	04:30 PM	1-Labor		10:04	10:04	0:00	10:04
52		[	Wed	2/24	06:30 AM	2/24	03:09 PM	1-Labor		8:39	8:39	0:00	8:39
53		[	Thu	2/25	07:14 AM	2/25	04:26 PM	1-Labor		9:12	9:12	0:00	9:12
54		[	Fri	2/26	06:27 AM	2/26	04:30 PM	1-Labor		10:03	10:03	0:00	10:03
55		[	Mon	3/1	06:26 AM	3/1	04:26 PM	1-Labor		10:00	2:02	7:58	10:00
56										47:58	40:00	7:58	47:58
57
58	10	[	Tue	3/2	06:30 AM	3/2	04:30 PM	1-Labor		10:00	10:00	0:00	10:00
59		[	Wed	3/3	06:27 AM	3/3	04:27 PM	1-Labor		10:00	10:00	0:00	10:00
60		[	Thu	3/4	06:30 AM	3/4	04:30 PM	1-Labor		10:00	10:00	0:00	10:00
61		[	Mon	3/8	06:24 AM	3/8	04:16 PM	1-Labor		9:52	9:52	0:00	9:52
62										39:52	39:52	0:00	39:52
63
64	11	[	Tue	3/9	06:30 AM	3/9	05:00 PM	1-Labor		10:30	10:30	0:00	10:30
65		[	Wed	3/10	06:20 AM	3/10	04:21 PM	1-Labor		10:01	10:01	0:00	10:01
66		[	Thu	3/11	06:26 AM	3/11	04:26 PM	1-Labor		10:00	10:00	0:00	10:00
67		[	Fri	3/12	08:00 AM	3/12	04:31 PM	1-Labor		8:31	8:31	0:00	8:31
68		[	Mon	3/15	06:26 AM	3/15	10:47 AM	1-Labor		4:21	0:58	3:23
69		[	Mon	3/15	11:06 AM	3/15	04:27 PM	1-Labor		5:21	0:00	5:21
70		[	Mon	3/15	04:29 PM	3/15	04:46 PM	1-Labor		0:17	0:00	0:17	9:59
71										49:01	40:00	9:01	49:01
72
73	12	[	Tue	3/16	06:26 AM	3/16	03:36 PM	1-Labor		9:10	9:10	0:00	9:10
74		[	Wed	3/17	08:32 AM	3/17	04:32 PM	1-Labor		8:00	8:00	0:00	8:00
75		[	Thu	3/18	06:24 AM	3/18	04:27 PM	1-Labor		10:03	10:03	0:00	10:03
76		[	Fri	3/19	07:00 AM	3/19	04:02 PM	1-Labor		9:02	9:02	0:00	9:02
77		[	Mon	3/22	06:27 AM	3/22	04:30 PM	1-Labor		10:03	3:45	6:18	10:03
78										46:18	40:00	6:18	46:18
79
80	13	[	Tue	3/23	06:12 AM	3/23	04:28 PM	1-Labor		10:16	10:16	0:00	10:16
81		[	Wed	3/24	06:19 AM	3/24	03:00 PM	1-Labor		8:41	8:41	0:00	8:41
82		[	Thu	3/25	06:15 AM	3/25	04:30 PM	1-Labor		10:15	10:15	0:00	10:15
83		[	Fri	3/26	06:25 AM	3/26	04:26 PM	1-Labor		10:01	10:01	0:00	10:01
84		[	Mon	3/29	06:30 AM	3/29	04:30 PM	1-Labor		10:00	0:47	9:13	10:00
85										49:13	40:00	9:13	49:13
86
87	14	[	Tue	3/30	06:28 AM	3/30	04:28 PM	1-Labor		10:00	10:00	0:00	10:00
88		[	Wed	3/31	06:26 AM	3/31	04:27 PM	1-Labor		10:01	10:01	0:00	10:01
89		[	Thu	4/1	06:20 AM	4/1	04:27 PM	1-Labor		10:07	10:07	0:00	10:07
90		[	Fri	4/2	06:25 AM	4/2	12:01 PM	1-Labor		5:36	5:36	0:00	5:36
91		[	Mon	4/5	06:28 AM	4/5	04:28 PM	1-Labor		10:00	4:16	5:44	10:00
92										45:44	40:00	5:44	45:44
93
94	15	[	Tue	4/6	06:27 AM	4/6	04:30 PM	1-Labor		10:03	10:03	0:00	10:03
95		[	Wed	4/7	08:45 AM	4/7	04:29 PM	1-Labor		7:44	7:44	0:00	7:44
96		[	Thu	4/8	09:47 AM	4/8	04:30 PM	1-Labor		6:43	6:43	0:00	6:43
97		[	Fri	4/9	06:26 AM	4/9	04:29 PM	1-Labor		10:03	10:03	0:00	10:03
98		[	Mon	4/12	06:28 AM	4/12	04:28 PM	1-Labor		10:00	5:27	4:33	10:00
99										44:33	40:00	4:33	44:33
100
101	16	[	Tue	4/13	06:24 AM	4/13	04:28 PM	1-Labor		10:04	10:04	0:00	10:04
102		[	Wed	4/14	06:26 AM	4/14	04:39 PM	1-Labor		10:13	10:13	0:00	10:13
103		[	Thu	4/15	06:22 AM	4/15	04:26 PM	1-Labor		10:04	10:04	0:00	10:04
104		[	Fri	4/16	06:29 AM	4/16	04:07 PM	1-Labor		9:38	9:38	0:00	9:38
105		[	Mon	4/19	06:23 AM	4/19	04:27 PM	1-Labor		10:04	0:01	10:03	10:04
106										50:03	40:00	10:03	50:03
107
108	17	[	Tue	4/20	06:26 AM	4/20	04:27 PM	1-Labor		10:01	10:01	0:00	10:01
109		[	Thu	4/22	06:20 AM	4/22	11:30 AM	1-Labor		5:10	5:10	0:00	5:10
110		[	Mon	4/26	06:23 AM	4/26	04:27 PM	1-Labor		10:04	10:04	0:00	10:04
111										25:15	25:15	0:00	25:15
112
113	18	[	Tue	4/27	06:25 AM	4/27	04:25 PM	1-Labor		10:00	10:00	0:00	10:00
114		[	Wed	4/28	06:25 AM	4/28	03:29 PM	1-Labor		9:04	9:04	0:00	9:04
115		[	Thu	4/29	06:31 AM	4/29	04:25 PM	1-Labor		9:54	9:54	0:00	9:54
116		[	Fri	4/30	06:25 AM	4/30	04:25 PM	1-Labor		10:00	10:00	0:00	10:00
117		[	Mon	5/3	06:37 AM	5/3	05:10 PM	1-Labor		10:33	1:02	9:31	10:33
118										49:31	40:00	9:31	49:31
Individual Analysis (2)

 

[earthworm]

The file size is too big to paste here.

In Step 1: I have organized the data .You now need to list all your requirement below so that I can add those as well.

Example what are the standards

What is your actual working hours
How many shift are required per week etc

Click here to download

 

[mediumrare]

Thank you for all of this, earthworm.

Standards
1. Actual working hours are 8:00 AM - 5:00 PM
2. Five (5) weekday shifts are required (M-F)

(Further) Shift Analysis
Job Codes (count)
List all non-labor job codes (e.g., holidays, leave)
List all missed weekday shifts
List all weekday shifts under # hr
Count all possible weekday shifts
Count all days worked
Count all missed possible weekday shifts
Average Time In

Since you have already started helping me, would it be too much to ask both (1) why you chose the functions you did for this task, and (2) (if you are willing) what struck you as "very messy." And whether I did anything good at all. Ha!

For example:

1. With the week column (Column O):
   1. Are you using the INDEX/MATCH functions and saying the logical test should be if they equal zero? As in, test if there is something there? If so, why?
   2. Why did you use the cell above it in Column O as the value if true?
2. With the other columns, I am trying to understand the whole argument (particularly in the row number section). I had no idea about the AGGREGATE function and it's very interesting!
   1. IFERROR(
      1. INDEX(
         1. array is the day of the week
         2. AGGREGATE is nested as the row (choosing the small function, and ignore errors option)
            1. ROW($A$5:$A$118)-ROW($A$5)+1/($A$5:$A$118<>"") <--what is this doing for the first argument?
            2. ROWS(P$5:P5) <-- what is this doing for the second argument?
3. What is the MATCH function doing with the Date In (that isn't happening for the other columns)?

One final note: the dates (date in/date out) are now incorrect. The dates were displaying as m-dd (albeit in text) and some of them have now flipped.

Again, if I haven't said it enough, I genuinely do thank you and appreciate your help thus far. I know I'm only scratching the surface of what's possible in Excel and I appreciate you helping me to learn to dig.

 

[earthworm]

Thank you for all of this, earthworm.

Standards
1. Actual working hours are 8:00 AM - 5:00 PM
2. Five (5) weekday shifts are required (M-F)

(Further) Shift Analysis
Job Codes (count)
List all non-labor job codes (e.g., holidays, leave)
List all missed weekday shifts
List all weekday shifts under # hr
Count all possible weekday shifts
Count all days worked
Count all missed possible weekday shifts
Average Time In

Since you have already started helping me, would it be too much to ask both (1) why you chose the functions you did for this task, and (2) (if you are willing) what struck you as "very messy." And whether I did anything good at all. Ha!

For example:

1. With the week column (Column O):
   1. Are you using the INDEX/MATCH functions and saying the logical test should be if they equal zero? As in, test if there is something there? If so, why?
   2. Why did you use the cell above it in Column O as the value if true?
2. With the other columns, I am trying to understand the whole argument (particularly in the row numbersection). I had no idea about the AGGREGATE function and it's very interesting!
   1. IFERROR(
      1. INDEX(
         1. array is the day of the week
         2. AGGREGATE is nested as the row (choosing the small function, and ignore errors option)
            1. ROW($A$5:$A$118)-ROW($A$5)+1/($A$5:$A$118<>"") <--what is this doing for the first argument?
            2. ROWS(P$5:P5) <-- what is this doing for the second argument?
3. What is the MATCH function doing with the Date In (that isn't happening for the other columns)?

One final note: the dates (date in/date out) are now incorrect. The dates were displaying as m-dd (albeit in text) and some of them have now flipped.

Again, if I haven't said it enough, I genuinely do thank you and appreciate your help thus far. I know I'm only scratching the surface of what's possible in Excel and I appreciate you helping me to learn to dig.

I used the word messy because your data is not organized so I have first organized the data in tabular form to make it more easy to understand.
Now you can apply logic easily because in your previous data everything is scattered in same sheet .

Anyway please clear me the below .

(Further) Shift Analysis
Job Codes (count)
What is this ?

List all weekday shifts under # hr
What is this ?

 

[earthworm]

Thank you for all of this, earthworm.

Standards
1. Actual working hours are 8:00 AM - 5:00 PM
2. Five (5) weekday shifts are required (M-F)

(Further) Shift Analysis
Job Codes (count)
List all non-labor job codes (e.g., holidays, leave)
List all missed weekday shifts
List all weekday shifts under # hr
Count all possible weekday shifts
Count all days worked
Count all missed possible weekday shifts
Average Time In

Since you have already started helping me, would it be too much to ask both (1) why you chose the functions you did for this task, and (2) (if you are willing) what struck you as "very messy." And whether I did anything good at all. Ha!

For example:

1. With the week column (Column O):
   1. Are you using the INDEX/MATCH functions and saying the logical test should be if they equal zero? As in, test if there is something there? If so, why?
   2. Why did you use the cell above it in Column O as the value if true?
2. With the other columns, I am trying to understand the whole argument (particularly in the row numbersection). I had no idea about the AGGREGATE function and it's very interesting!
   1. IFERROR(
      1. INDEX(
         1. array is the day of the week
         2. AGGREGATE is nested as the row (choosing the small function, and ignore errors option)
            1. ROW($A$5:$A$118)-ROW($A$5)+1/($A$5:$A$118<>"") <--what is this doing for the first argument?
            2. ROWS(P$5:P5) <-- what is this doing for the second argument?
3. What is the MATCH function doing with the Date In (that isn't happening for the other columns)?

One final note: the dates (date in/date out) are now incorrect. The dates were displaying as m-dd (albeit in text) and some of them have now flipped.

Again, if I haven't said it enough, I genuinely do thank you and appreciate your help thus far. I know I'm only scratching the surface of what's possible in Excel and I appreciate you helping me to learn to dig.

Lets me just build your requirement and then let me guide in next phase.

The date format on my PC is DD-MM-YYYY we will solve this part later.

 

[mediumrare]

I used the word messy because your data is not organized so I have first organized the data in tabular form to make it more easy to understand.
Now you can apply logic easily because in your previous data everything is scattered in same sheet .

Anyway please clear me the below .

(Further) Shift Analysis
Job Codes (count)
What is this ?

List all weekday shifts under # hr
What is this ?

1. I wanted to be able to count the Job Codes (column H) -- 3 holidays, 5 vacation leave, etc.

2. I wanted to be able to pull all the weekday shifts (1-Labor) that were less than x number of hours, where x is defined by a helper cell.

 

[earthworm]

I have tried my best what I have understand please check

Click here to download

I leave the rest remaining part with other experts.

 

[mediumrare]

Could I ask you another question or should I start a new thread? Trying to merge two lists and keep only the unique dates.

HR Analysis - Beta Test.xlsx
	AC	AD
4	Days Worked	2021 Weekdays
5	01/01/21	01/01/21
6	01/11/21	01/04/21
7	01/12/21	01/05/21
8	01/13/21	01/06/21
9	01/14/21	01/07/21
10	01/15/21	01/08/21
11	01/18/21	01/11/21
12	01/19/21	01/12/21
13	01/20/21	01/13/21
14	01/21/21	01/14/21
15	01/22/21	01/15/21
16	01/25/21	01/18/21
17	01/27/21	01/19/21
18	01/28/21	01/20/21
19	01/29/21	01/21/21
20	02/02/21	01/22/21
21	02/03/21	01/25/21
22	02/04/21	01/26/21
23	02/05/21	01/27/21
24	02/08/21	01/28/21
25	02/09/21	01/29/21
26	02/10/21	02/01/21
27	02/11/21	02/02/21
28	02/12/21	02/03/21
29	02/18/21	02/04/21
30	02/19/21	02/05/21
31	02/20/21	02/08/21
32	02/22/21	02/09/21
33	02/23/21	02/10/21
34	02/24/21	02/11/21
35	02/25/21	02/12/21
36	02/26/21	02/15/21
37	03/01/21	02/16/21
38	03/02/21	02/17/21
39	03/03/21	02/18/21
40	03/04/21	02/19/21
41	03/08/21	02/22/21
42	03/09/21	02/23/21
43	03/10/21	02/24/21
44	03/11/21	02/25/21
45	03/12/21	02/26/21
46	03/15/21	03/01/21
47	03/15/21	03/02/21
48	03/15/21	03/03/21
49	03/16/21	03/04/21
50	03/17/21	03/05/21
51	03/18/21	03/08/21
52	03/19/21	03/09/21
53	03/22/21	03/10/21
54	03/23/21	03/11/21
55	03/24/21	03/12/21
56	03/25/21	03/15/21
57	03/26/21	03/16/21
58	03/29/21	03/17/21
59	03/30/21	03/18/21
60	03/31/21	03/19/21
61	04/01/21	03/22/21
62	04/02/21	03/23/21
63	04/05/21	03/24/21
64	04/06/21	03/25/21
65	04/07/21	03/26/21
66	04/08/21	03/29/21
67	04/09/21	03/30/21
68	04/12/21	03/31/21
69	04/13/21	04/01/21
70	04/14/21	04/02/21
71	04/15/21	04/05/21
72	04/16/21	04/06/21
73	04/19/21	04/07/21
74	04/20/21	04/08/21
75	04/22/21	04/09/21
76	04/26/21	04/12/21
77	04/27/21	04/13/21
78	04/28/21	04/14/21
79	04/29/21	04/15/21
80	04/30/21	04/16/21
81	05/03/21	04/19/21
82	05/04/21	04/20/21
83	05/05/21	04/21/21
84	05/06/21	04/22/21
85	05/07/21	04/23/21
86	05/10/21	04/26/21
87	05/11/21	04/27/21
88	05/12/21	04/28/21
89	05/18/21	04/29/21
90	05/19/21	04/30/21
91	05/20/21	05/03/21
92	05/21/21	05/04/21
93	05/24/21	05/05/21
94	05/25/21	05/06/21
95	05/26/21	05/07/21
96	05/27/21	05/10/21
97	05/28/21	05/11/21
98	05/29/21	05/12/21
99	05/31/21	05/13/21
100	06/01/21	05/14/21
101	06/03/21	05/17/21
102	06/04/21	05/18/21
103	06/07/21	05/19/21
104	06/08/21	05/20/21
105	06/09/21	05/21/21
106	06/14/21	05/24/21
107	06/14/21	05/25/21
108	06/15/21	05/26/21
109	06/16/21	05/27/21
110	06/17/21	05/28/21
111	06/18/21	05/31/21
112	06/21/21	06/01/21
113	06/22/21	06/02/21
114	06/23/21	06/03/21
115	06/24/21	06/04/21
116	06/25/21	06/07/21
117	06/28/21	06/08/21
118	06/30/21	06/09/21
119	07/01/21	06/10/21
120	07/02/21	06/11/21
121	07/05/21	06/14/21
122	07/06/21	06/15/21
123	07/07/21	06/16/21
124	07/08/21	06/17/21
125	07/09/21	06/18/21
126	07/12/21	06/21/21
127	07/13/21	06/22/21
128	07/14/21	06/23/21
129	07/15/21	06/24/21
130	07/16/21	06/25/21
131	07/19/21	06/28/21
132	07/20/21	06/29/21
133	07/20/21	06/30/21
134	07/21/21	07/01/21
135	07/22/21	07/02/21
136	07/23/21	07/05/21
137	07/26/21	07/06/21
138	07/27/21	07/07/21
139	07/28/21	07/08/21
140	07/29/21	07/09/21
141	07/30/21	07/12/21
142	08/02/21	07/13/21
143	08/03/21	07/14/21
144	08/04/21	07/15/21
145	08/05/21	07/16/21
146	08/06/21	07/19/21
147	08/19/21	07/20/21
148	08/20/21	07/21/21
149	08/23/21	07/22/21
150	08/24/21	07/23/21
151	08/25/21	07/26/21
152	08/26/21	07/27/21
153	08/27/21	07/28/21
154	08/30/21	07/29/21
155	09/01/21	07/30/21
156	09/02/21	08/02/21
157	09/03/21	08/03/21
158		08/04/21
159		08/05/21
160		08/06/21
161		08/09/21
162		08/10/21
163		08/11/21
164		08/12/21
165		08/13/21
166		08/16/21
167		08/17/21
168		08/18/21
169		08/19/21
170		08/20/21
171		08/23/21
172		08/24/21
173		08/25/21
174		08/26/21
175		08/27/21
176		08/30/21
177		08/31/21
178		09/01/21
179		09/02/21
180		09/03/21
181		09/06/21
182		09/07/21
183		09/08/21
184		09/09/21
185		09/10/21
186		09/13/21
187		09/14/21
188		09/15/21
189		09/16/21
190		09/17/21
191		09/20/21
192		09/21/21
193		09/22/21
194		09/23/21
195		09/24/21
196		09/27/21
197		09/28/21
198		09/29/21
199		09/30/21
200		10/01/21
201		10/04/21
202		10/05/21
203		10/06/21
204		10/07/21
205		10/08/21
206		10/11/21
207		10/12/21
208		10/13/21
209		10/14/21
210		10/15/21
211		10/18/21
212		10/19/21
213		10/20/21
214		10/21/21
215		10/22/21
216		10/25/21
217		10/26/21
218		10/27/21
219		10/28/21
220		10/29/21
221		11/01/21
222		11/02/21
223		11/03/21
224		11/04/21
225		11/05/21
226		11/08/21
227		11/09/21
228		11/10/21
229		11/11/21
230		11/12/21
231		11/15/21
232		11/16/21
233		11/17/21
234		11/18/21
235		11/19/21
236		11/22/21
237		11/23/21
238		11/24/21
239		11/25/21
240		11/26/21
241		11/29/21
242		11/30/21
243		12/01/21
244		12/02/21
245		12/03/21
246		12/06/21
247		12/07/21
248		12/08/21
249		12/09/21
250		12/10/21
251		12/13/21
252		12/14/21
253		12/15/21
254		12/16/21
255		12/17/21
256		12/20/21
257		12/21/21
258		12/22/21
259		12/23/21
260		12/24/21
261		12/27/21
262		12/28/21
263		12/29/21
264		12/30/21
265		12/31/21
analysis

Wanting a cleaner way to look at list 1 and list 2 and produce a new list with only the unique values. I'm sure I'm just missing something here...