Improving my formulas/approach for date/time analysis

mediumrare

New Member
Joined
Apr 7, 2021
Messages
31
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLM
1Payroll Detail
2For the period of 1/1/2021 to 9/5/2021
3DateTimeDateTime
4WeekDayInInOutOutJob CodeBreakHoursRegOvt1Day Total
5
6
71[Fri1/108:00 AM------11-Holiday8:008:000:008:00
88:008:000:008:00
9
102[Mon1/1106:29 AM1/1104:28 PM1-Labor9:599:590:009:59
119:599:590:009:59
12
133[Tue1/1206:28 AM1/1204:28 PM1-Labor10:0010:000:0010:00
14[Wed1/1306:00 AM1/1304:25 PM1-Labor10:2510:250:0010:25
15[Thu1/1406:28 AM1/1404:30 PM1-Labor10:0210:020:0010:02
16[Fri1/1506:27 AM1/1503:30 PM1-Labor9:039:030:009:03
17[Mon1/1806:27 AM1/1804:22 PM1-Labor9:550:309:259:55
1849:2540:009:2549:25
19
204[Tue1/1906:31 AM1/1904:28 PM1-Labor9:579:570:009:57
21[Wed1/2006:27 AM1/2004:26 PM1-Labor9:599:590:009:59
22[Thu1/2107:16 AM1/2104:45 PM1-Labor9:299:290:009:29
23[Fri1/2206:27 AM1/2204:27 PM1-Labor10:0010:000:0010:00
24[Mon1/2507:09 AM1/2504:30 PM1-Labor9:210:358:469:21
2548:4640:008:4648:46
26
275[Wed1/2706:27 AM1/2704:25 PM1-Labor9:589:580:009:58
28[Thu1/2806:29 AM1/2804:24 PM1-Labor9:559:550:009:55
29[Fri1/2906:30 AM1/2904:27 PM1-Labor9:579:570:009:57
3029:5029:500:0029:50
Individual Analysis (2)


And here is a sample of how I currently transform it.
hours analysis deID.xlsx
OPQRSTUVWXYZAAABACADAEAFAGAH
1Instructions
21. copy XML of individual's hours to be analyzed
32. make sure you adjust the ranges in your formulas (see red tabs for notes) (datein, timemin, timein, daysworked, jobcode)
4Collecting Information for Analysis3. you can edit the named ranges: Formulas > Name Manager
54. make sure you remember to empty all non-date cells in Column O, otherwise formulas will give you #VALUE errors
6Date InDate OutTime (Min)Time InYear 2021#Day
701/01/21 48008:00 AM01/01/215Fri0:008:0010:59
8480 01/02/216Sat1234567#
9  01/03/217SunAverage Time In7:01 AMMonTueWedThuFriSatSunDay of the Week
1001/11/2101/11/2159906:29 AM01/04/211Mon3535353536Days in Period
11599 01/05/212TueJob Codes (count)292829313020Days Worked in Period
12  01/06/213Wed11-Holiday367646Difference
1301/12/2101/12/2160006:28 AM01/07/214Thu1-Labor145
1401/13/2101/13/2162506:00 AM01/08/215Fri2-Vacation Leave5147/176weekday shifts worked
1501/14/2101/14/2160206:28 AM01/09/216Sat29weekday shifts missed
1601/15/2101/15/2154306:27 AM01/10/217Sunnon-labor codesearly clock-in detaillate clock-in detail
1701/18/2101/18/2159506:27 AM01/11/211Mon11-HolidayJan 01 8:00 AMx12:00 PMx
182965 01/12/212TueMay 31 7:50 AM111:00 AM1
19  01/13/213WedJul 05 7:40 AM210:00 AM0
2001/19/2101/19/2159706:31 AM01/14/214Thu2-Vacation LeaveAug 02 7:30 AM39:00 AM4
2101/20/2101/20/2159906:27 AM01/15/215FriAug 03 7:20 AM28:30 AM2
2201/21/2101/21/2156907:16 AM01/16/216SatAug 04 7:10 AM68:15 AM0
2301/22/2101/22/2160006:27 AM01/17/217SunAug 05 7:00 AM58:05 AM1
2401/25/2101/25/2156107:09 AM01/18/211MonAug 06 <7:00 AM1128:00 AM10
252926 01/19/212Tue
26  01/20/213Wed# of shifts less than 6 hr7missed & short weekday shifts
2701/27/2101/27/2159806:27 AM01/21/214Thuenter hr amt here6datedaydetails
2801/28/2101/28/2159506:29 AM01/22/215Fridateminhh:mmday01/04/21Monmissed
2901/29/2101/29/2159706:30 AM01/23/216SatSat. Feb 20 18003:00Sat01/05/21Tuemissed
301790 01/24/217SunFri. Apr 02 33605:36Fri01/06/21Wedmissed
31  01/25/211MonThu. Apr 22 31005:10Thu01/07/21Thumissed
3202/02/2102/02/2160306:27 AM01/26/212TueSat. May 29 18003:00Sat01/08/21Frimissed
3302/03/2102/03/2159806:28 AM01/27/213WedFri. Jun 18 22803:48Fri01/26/21Tuemissed
3402/04/2102/04/2160006:26 AM01/28/214ThuMon. Jun 28 23803:58Mon02/01/21Monmissed
3502/05/2102/05/2160106:25 AM01/29/215FriThu. Jul 15 30005:00Thu02/15/21Monmissed
3602/08/2102/08/2160106:29 AM01/30/216Sat  02/16/21Tuemissed
Individual Analysis (2)
Cell Formulas
RangeFormula
O7,O32:O36,O27:O29,O20:O24,O13:O17,O10O7=IF(ISBLANK(D7),"",DATEVALUE(D7))
P7,P32:P36,P27:P29,P20:P24,P13:P17,P10P7=IFERROR((DATEVALUE(F7)),"")
Q7:Q36Q7=IFERROR(1/(1/((TEXT(M7,"[hh]:mm")*1440))),"")
R7:R36R7=IFERROR(1/(1/(E7)),"")
U7:U36U7=WEEKDAY(T7,2)
V7:V36V7=TEXT(T7,"ddd")
Y9Y9=AVERAGE(timein)
AA10:AE10AA10=SUMPRODUCT((WEEKDAY(daysworked,2)=AA8)*(daysworked<>""))
AA11:AG11AA11=SUMPRODUCT((WEEKDAY(datein,2)=AA8)*(datein<>"")/COUNTIF(datein,datein&""))
X12:X14X12=UNIQUE(FILTER(jobcode,jobcode<>""))
AA12:AE12AA12=AA10-AA11
Y12:Y14Y12=COUNTIF(jobcode,X12)
AG14AG14=CONCATENATE(SUM(AA11:AE11),"/",SUM(AA10:AE10))
AG15AG15=SUM(AA12:AE12)
X17X17=X12
Y17:Y19Y17=DATEVALUE(FILTER(D7:D227,jobcode=X17))
X20X20=X14
Y20:Y24Y20=DATEVALUE(FILTER(D7:D227,jobcode=X20))
AB18AB18=COUNTIFS(R7:R227,">="&$AA18,R7:R227,"<"&AA17)
AB19AB19=COUNTIFS(R7:R227,">="&AA19,R7:R227,"<"&AA18)
AB20AB20=COUNTIFS(R7:R227,">="&AA20,R7:R227,"<"&AA19)
AB21AB21=COUNTIFS(R7:R227,">="&AA21,R7:R227,"<"&AA20)
AB22AB22=COUNTIFS(R7:R227,">="&AA22,R7:R227,"<"&AA21)
AB23AB23=COUNTIFS(R7:R227,">="&AA23,R7:R227,"<"&AA22)
AB24AB24=COUNTIF(R7:R227,"<"&AA23)
AE18AE18=COUNTIFS(R7:R227,">="&AD18,R7:R227,"<"&AD17)
AE19AE19=COUNTIFS(R7:R227,">="&AD19,R7:R227,"<"&AD18)
AE20AE20=COUNTIFS(R7:R227,">="&AD20,R7:R227,"<"&AD19)
AE21AE21=COUNTIFS(R7:R227,">="&AD21,R7:R227,"<"&AD20)
AE22AE22=COUNTIFS(R7:R227,">="&AD13,R7:R227,"<"&AD21)
AE23AE23=COUNTIFS(R7:R227,">="&AD23,R7:R227,"<"&AD22)
AE24AE24=COUNTIFS(R7:R227,">="&AD24,R7:R227,"<"&AD23)
X26X26=CONCATENATE("# of shifts less than"," ",Y27," hr")
Y26Y26=COUNT(Y29:Y371)
X29:Y35X29=UNIQUE(FILTER(P7:Q200,Q7:Q200<(Y27*60)))
Z29:Z34Z29=Y29/1440
AA29:AA34AA29=TEXT(X29,"ddd")
Z35:Z36Z35=IF(ISBLANK(Y35),"",Y35/1440)
AA35:AA36AA35=IF(ISBLANK(Y35),"",TEXT(X35,"ddd"))
AD28:AD36AD28=TEXT(AC28,"ddd")
Dynamic array formulas.
Named Ranges
NameRefers ToCells
'Individual Analysis (2)'!datein='Individual Analysis (2)'!$O$7:$O$371AA11:AG11
'Individual Analysis (2)'!daysworked='Individual Analysis (2)'!$T$7:$T$252AA10:AE10, U7:V7
'Individual Analysis (2)'!jobcode='Individual Analysis (2)'!$H$7:$H$227Y20, Y17, X12, Y12:Y14
'Individual Analysis (2)'!timein='Individual Analysis (2)'!$R$7:$R$371AB18:AB24, AE18:AE24, Y9
'Individual Analysis (2)'!timemin='Individual Analysis (2)'!$Q$7:$Q$371X29
'Individual Analysis (2)'!Year2021='Individual Analysis (2)'!$T$7:$T$371AA10:AE10, U7:V7
Cells with Conditional Formatting
CellConditionCell FormatStop If True
T7:T371Expression=MATCH(T7,datein,0)textNO
Q7:S371Cell Valuebetween 1 and 240textNO
U7:V371Expression=WEEKDAY($U7,1)<=5textNO
O7:P7,O9:P371,P8Expression=$O7<>$P7textNO
P1Expression=$P1<>$P8textNO


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)
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

earthworm

Well-known Member
Joined
May 19, 2009
Messages
529
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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

New Member
Joined
Apr 7, 2021
Messages
31
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLM
1Payroll Detail
2For the period of 1/1/2021 to 9/5/2021
3DateTimeDateTime
4WeekDayInInOutOutJob CodeBreakHoursRegOvt1Day Total
5
6
71[Fri1/108:00 AM------11-Holiday8:008:000:008:00
88:008:000:008:00
9
102[Mon1/1106:29 AM1/1104:28 PM1-Labor9:599:590:009:59
119:599:590:009:59
12
133[Tue1/1206:28 AM1/1204:28 PM1-Labor10:0010:000:0010:00
14[Wed1/1306:00 AM1/1304:25 PM1-Labor10:2510:250:0010:25
15[Thu1/1406:28 AM1/1404:30 PM1-Labor10:0210:020:0010:02
16[Fri1/1506:27 AM1/1503:30 PM1-Labor9:039:030:009:03
17[Mon1/1806:27 AM1/1804:22 PM1-Labor9:550:309:259:55
1849:2540:009:2549:25
19
204[Tue1/1906:31 AM1/1904:28 PM1-Labor9:579:570:009:57
21[Wed1/2006:27 AM1/2004:26 PM1-Labor9:599:590:009:59
22[Thu1/2107:16 AM1/2104:45 PM1-Labor9:299:290:009:29
23[Fri1/2206:27 AM1/2204:27 PM1-Labor10:0010:000:0010:00
24[Mon1/2507:09 AM1/2504:30 PM1-Labor9:210:358:469:21
2548:4640:008:4648:46
26
275[Wed1/2706:27 AM1/2704:25 PM1-Labor9:589:580:009:58
28[Thu1/2806:29 AM1/2804:24 PM1-Labor9:559:550:009:55
29[Fri1/2906:30 AM1/2904:27 PM1-Labor9:579:570:009:57
3029:5029:500:0029:50
31
326[Tue2/206:27 AM2/204:30 PM1-Labor10:0310:030:0010:03
33[Wed2/306:28 AM2/304:26 PM1-Labor9:589:580:009:58
34[Thu2/406:26 AM2/404:26 PM1-Labor10:0010:000:0010:00
35[Fri2/506:25 AM2/504:26 PM1-Labor10:019:590:0210:01
36[Mon2/806:29 AM2/804:30 PM1-Labor10:010:0010:0110:01
3750:0340:0010:0350:03
38
397[Tue2/906:15 AM2/904:30 PM1-Labor10:1510:150:0010:15
40[Wed2/1006:25 AM2/1004:25 PM1-Labor10:0010:000:0010:00
41[Thu2/1106:20 AM2/1104:20 PM1-Labor10:0010:000:0010:00
42[Fri2/1206:25 AM2/1204:30 PM1-Labor10:059:450:2010:05
4340:2040:000:2040:20
44
458[Thu2/1806:50 AM2/1804:00 PM1-Labor9:109:100:009:10
46[Fri2/1907:40 AM2/1904:30 PM1-Labor8:508:500:008:50
47[Sat2/2007:30 AM2/2010:30 AM1-Labor3:003:000:003:00
48[Mon2/2207:30 AM2/2204:30 PM1-Labor9:009:000:009:00
4930:0030:000:0030:00
50
519[Tue2/2306:26 AM2/2304:30 PM1-Labor10:0410:040:0010:04
52[Wed2/2406:30 AM2/2403:09 PM1-Labor8:398:390:008:39
53[Thu2/2507:14 AM2/2504:26 PM1-Labor9:129:120:009:12
54[Fri2/2606:27 AM2/2604:30 PM1-Labor10:0310:030:0010:03
55[Mon3/106:26 AM3/104:26 PM1-Labor10:002:027:5810:00
5647:5840:007:5847:58
57
5810[Tue3/206:30 AM3/204:30 PM1-Labor10:0010:000:0010:00
59[Wed3/306:27 AM3/304:27 PM1-Labor10:0010:000:0010:00
60[Thu3/406:30 AM3/404:30 PM1-Labor10:0010:000:0010:00
61[Mon3/806:24 AM3/804:16 PM1-Labor9:529:520:009:52
6239:5239:520:0039:52
63
6411[Tue3/906:30 AM3/905:00 PM1-Labor10:3010:300:0010:30
65[Wed3/1006:20 AM3/1004:21 PM1-Labor10:0110:010:0010:01
66[Thu3/1106:26 AM3/1104:26 PM1-Labor10:0010:000:0010:00
67[Fri3/1208:00 AM3/1204:31 PM1-Labor8:318:310:008:31
68[Mon3/1506:26 AM3/1510:47 AM1-Labor4:210:583:23
69[Mon3/1511:06 AM3/1504:27 PM1-Labor5:210:005:21
70[Mon3/1504:29 PM3/1504:46 PM1-Labor0:170:000:179:59
7149:0140:009:0149:01
72
7312[Tue3/1606:26 AM3/1603:36 PM1-Labor9:109:100:009:10
74[Wed3/1708:32 AM3/1704:32 PM1-Labor8:008:000:008:00
75[Thu3/1806:24 AM3/1804:27 PM1-Labor10:0310:030:0010:03
76[Fri3/1907:00 AM3/1904:02 PM1-Labor9:029:020:009:02
77[Mon3/2206:27 AM3/2204:30 PM1-Labor10:033:456:1810:03
7846:1840:006:1846:18
79
8013[Tue3/2306:12 AM3/2304:28 PM1-Labor10:1610:160:0010:16
81[Wed3/2406:19 AM3/2403:00 PM1-Labor8:418:410:008:41
82[Thu3/2506:15 AM3/2504:30 PM1-Labor10:1510:150:0010:15
83[Fri3/2606:25 AM3/2604:26 PM1-Labor10:0110:010:0010:01
84[Mon3/2906:30 AM3/2904:30 PM1-Labor10:000:479:1310:00
8549:1340:009:1349:13
86
8714[Tue3/3006:28 AM3/3004:28 PM1-Labor10:0010:000:0010:00
88[Wed3/3106:26 AM3/3104:27 PM1-Labor10:0110:010:0010:01
89[Thu4/106:20 AM4/104:27 PM1-Labor10:0710:070:0010:07
90[Fri4/206:25 AM4/212:01 PM1-Labor5:365:360:005:36
91[Mon4/506:28 AM4/504:28 PM1-Labor10:004:165:4410:00
9245:4440:005:4445:44
93
9415[Tue4/606:27 AM4/604:30 PM1-Labor10:0310:030:0010:03
95[Wed4/708:45 AM4/704:29 PM1-Labor7:447:440:007:44
96[Thu4/809:47 AM4/804:30 PM1-Labor6:436:430:006:43
97[Fri4/906:26 AM4/904:29 PM1-Labor10:0310:030:0010:03
98[Mon4/1206:28 AM4/1204:28 PM1-Labor10:005:274:3310:00
9944:3340:004:3344:33
100
10116[Tue4/1306:24 AM4/1304:28 PM1-Labor10:0410:040:0010:04
102[Wed4/1406:26 AM4/1404:39 PM1-Labor10:1310:130:0010:13
103[Thu4/1506:22 AM4/1504:26 PM1-Labor10:0410:040:0010:04
104[Fri4/1606:29 AM4/1604:07 PM1-Labor9:389:380:009:38
105[Mon4/1906:23 AM4/1904:27 PM1-Labor10:040:0110:0310:04
10650:0340:0010:0350:03
107
10817[Tue4/2006:26 AM4/2004:27 PM1-Labor10:0110:010:0010:01
109[Thu4/2206:20 AM4/2211:30 AM1-Labor5:105:100:005:10
110[Mon4/2606:23 AM4/2604:27 PM1-Labor10:0410:040:0010:04
11125:1525:150:0025:15
112
11318[Tue4/2706:25 AM4/2704:25 PM1-Labor10:0010:000:0010:00
114[Wed4/2806:25 AM4/2803:29 PM1-Labor9:049:040:009:04
115[Thu4/2906:31 AM4/2904:25 PM1-Labor9:549:540:009:54
116[Fri4/3006:25 AM4/3004:25 PM1-Labor10:0010:000:0010:00
117[Mon5/306:37 AM5/305:10 PM1-Labor10:331:029:3110:33
11849:3140:009:3149:31
Individual Analysis (2)
 

earthworm

Well-known Member
Joined
May 19, 2009
Messages
529
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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

New Member
Joined
Apr 7, 2021
Messages
31
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
May 19, 2009
Messages
529
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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

Well-known Member
Joined
May 19, 2009
Messages
529
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Apr 7, 2021
Messages
31
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
May 19, 2009
Messages
529
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
I have tried my best what I have understand please check

Click here to download

I leave the rest remaining part with other experts.
 
Solution

mediumrare

New Member
Joined
Apr 7, 2021
Messages
31
Office Version
  1. 365
Platform
  1. Windows
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
ACAD
4Days Worked2021 Weekdays
501/01/2101/01/21
601/11/2101/04/21
701/12/2101/05/21
801/13/2101/06/21
901/14/2101/07/21
1001/15/2101/08/21
1101/18/2101/11/21
1201/19/2101/12/21
1301/20/2101/13/21
1401/21/2101/14/21
1501/22/2101/15/21
1601/25/2101/18/21
1701/27/2101/19/21
1801/28/2101/20/21
1901/29/2101/21/21
2002/02/2101/22/21
2102/03/2101/25/21
2202/04/2101/26/21
2302/05/2101/27/21
2402/08/2101/28/21
2502/09/2101/29/21
2602/10/2102/01/21
2702/11/2102/02/21
2802/12/2102/03/21
2902/18/2102/04/21
3002/19/2102/05/21
3102/20/2102/08/21
3202/22/2102/09/21
3302/23/2102/10/21
3402/24/2102/11/21
3502/25/2102/12/21
3602/26/2102/15/21
3703/01/2102/16/21
3803/02/2102/17/21
3903/03/2102/18/21
4003/04/2102/19/21
4103/08/2102/22/21
4203/09/2102/23/21
4303/10/2102/24/21
4403/11/2102/25/21
4503/12/2102/26/21
4603/15/2103/01/21
4703/15/2103/02/21
4803/15/2103/03/21
4903/16/2103/04/21
5003/17/2103/05/21
5103/18/2103/08/21
5203/19/2103/09/21
5303/22/2103/10/21
5403/23/2103/11/21
5503/24/2103/12/21
5603/25/2103/15/21
5703/26/2103/16/21
5803/29/2103/17/21
5903/30/2103/18/21
6003/31/2103/19/21
6104/01/2103/22/21
6204/02/2103/23/21
6304/05/2103/24/21
6404/06/2103/25/21
6504/07/2103/26/21
6604/08/2103/29/21
6704/09/2103/30/21
6804/12/2103/31/21
6904/13/2104/01/21
7004/14/2104/02/21
7104/15/2104/05/21
7204/16/2104/06/21
7304/19/2104/07/21
7404/20/2104/08/21
7504/22/2104/09/21
7604/26/2104/12/21
7704/27/2104/13/21
7804/28/2104/14/21
7904/29/2104/15/21
8004/30/2104/16/21
8105/03/2104/19/21
8205/04/2104/20/21
8305/05/2104/21/21
8405/06/2104/22/21
8505/07/2104/23/21
8605/10/2104/26/21
8705/11/2104/27/21
8805/12/2104/28/21
8905/18/2104/29/21
9005/19/2104/30/21
9105/20/2105/03/21
9205/21/2105/04/21
9305/24/2105/05/21
9405/25/2105/06/21
9505/26/2105/07/21
9605/27/2105/10/21
9705/28/2105/11/21
9805/29/2105/12/21
9905/31/2105/13/21
10006/01/2105/14/21
10106/03/2105/17/21
10206/04/2105/18/21
10306/07/2105/19/21
10406/08/2105/20/21
10506/09/2105/21/21
10606/14/2105/24/21
10706/14/2105/25/21
10806/15/2105/26/21
10906/16/2105/27/21
11006/17/2105/28/21
11106/18/2105/31/21
11206/21/2106/01/21
11306/22/2106/02/21
11406/23/2106/03/21
11506/24/2106/04/21
11606/25/2106/07/21
11706/28/2106/08/21
11806/30/2106/09/21
11907/01/2106/10/21
12007/02/2106/11/21
12107/05/2106/14/21
12207/06/2106/15/21
12307/07/2106/16/21
12407/08/2106/17/21
12507/09/2106/18/21
12607/12/2106/21/21
12707/13/2106/22/21
12807/14/2106/23/21
12907/15/2106/24/21
13007/16/2106/25/21
13107/19/2106/28/21
13207/20/2106/29/21
13307/20/2106/30/21
13407/21/2107/01/21
13507/22/2107/02/21
13607/23/2107/05/21
13707/26/2107/06/21
13807/27/2107/07/21
13907/28/2107/08/21
14007/29/2107/09/21
14107/30/2107/12/21
14208/02/2107/13/21
14308/03/2107/14/21
14408/04/2107/15/21
14508/05/2107/16/21
14608/06/2107/19/21
14708/19/2107/20/21
14808/20/2107/21/21
14908/23/2107/22/21
15008/24/2107/23/21
15108/25/2107/26/21
15208/26/2107/27/21
15308/27/2107/28/21
15408/30/2107/29/21
15509/01/2107/30/21
15609/02/2108/02/21
15709/03/2108/03/21
15808/04/21
15908/05/21
16008/06/21
16108/09/21
16208/10/21
16308/11/21
16408/12/21
16508/13/21
16608/16/21
16708/17/21
16808/18/21
16908/19/21
17008/20/21
17108/23/21
17208/24/21
17308/25/21
17408/26/21
17508/27/21
17608/30/21
17708/31/21
17809/01/21
17909/02/21
18009/03/21
18109/06/21
18209/07/21
18309/08/21
18409/09/21
18509/10/21
18609/13/21
18709/14/21
18809/15/21
18909/16/21
19009/17/21
19109/20/21
19209/21/21
19309/22/21
19409/23/21
19509/24/21
19609/27/21
19709/28/21
19809/29/21
19909/30/21
20010/01/21
20110/04/21
20210/05/21
20310/06/21
20410/07/21
20510/08/21
20610/11/21
20710/12/21
20810/13/21
20910/14/21
21010/15/21
21110/18/21
21210/19/21
21310/20/21
21410/21/21
21510/22/21
21610/25/21
21710/26/21
21810/27/21
21910/28/21
22010/29/21
22111/01/21
22211/02/21
22311/03/21
22411/04/21
22511/05/21
22611/08/21
22711/09/21
22811/10/21
22911/11/21
23011/12/21
23111/15/21
23211/16/21
23311/17/21
23411/18/21
23511/19/21
23611/22/21
23711/23/21
23811/24/21
23911/25/21
24011/26/21
24111/29/21
24211/30/21
24312/01/21
24412/02/21
24512/03/21
24612/06/21
24712/07/21
24812/08/21
24912/09/21
25012/10/21
25112/13/21
25212/14/21
25312/15/21
25412/16/21
25512/17/21
25612/20/21
25712/21/21
25812/22/21
25912/23/21
26012/24/21
26112/27/21
26212/28/21
26312/29/21
26412/30/21
26512/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...
 

Forum statistics

Threads
1,144,572
Messages
5,725,071
Members
422,590
Latest member
Mikeyyy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top