mediumrare
New Member
- Joined
- Apr 7, 2021
- Messages
- 31
- Office Version
- 365
- Platform
- 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:
And here is a sample of how I currently transform it.
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)
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)