alm395
New Member
- Joined
- Apr 23, 2018
- Messages
- 39
- Office Version
- 365
- Platform
- Windows
Attached is an extremely condensed version of a report that I have to run monthly. Yesterday I was asked if the Master page could be filtered based on a date range, instead of the full data from the start date through the date the report is updated.
I have added a "Start Date" and "End Date" section, but am not sure how to make this work on the main report, the position report (on the side of the main report), and on the manager report (below the position report).
If this is possible, please help. Pretty please?! The actual report includes around 600 employees, so I would really hate to recreate this for specific dates each time the request is made.
If there is a way to upload a small version of my workbook, please let me know and I will do so.
Thanks sooooo much!
Person1 Tab
Person2 Tab
I have added a "Start Date" and "End Date" section, but am not sure how to make this work on the main report, the position report (on the side of the main report), and on the manager report (below the position report).
If this is possible, please help. Pretty please?! The actual report includes around 600 employees, so I would really hate to recreate this for specific dates each time the request is made.
If there is a way to upload a small version of my workbook, please let me know and I will do so.
Thanks sooooo much!
FY22 - AVAIL REPORT.xlsm | ||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | |||
1 | Availability Report by Individual | START DATE: | 4/23/2021 00:00:00 | |||||||||||||
2 | Feb 01, 2021 - Jun 11, 2021 | END DATE: | 4/24/2021 00:00:00 | |||||||||||||
3 | EXCLUDING CALL DUTY | |||||||||||||||
4 | VruID | Name | Site | Class | Manager | Status | Total Worked | Total Not Worked | Total | Including CD | Total Worked (ECD) | Total Not Worked (ECD) | Total (ECD) | Excluding CD | ||
5 | 111 | PERSON1 | 1 | POS1 | MGR1 | Active | 6 | 1 | 7 | 86% | 2 | 0 | 2 | 100% | ||
6 | 222 | PERSON2 | 1 | POS2 | MGR2 | Active | 7 | 11 | 18 | 39% | 3 | 11 | 14 | 21% | ||
7 | 333 | PERSON3 | 2 | POS3 | MGR3 | Active | 7 | 0 | 7 | 100% | 0 | 0 | 0 | - | ||
8 | 444 | PERSON5 | 2 | POS4 | MGR2 | Active | 6 | 3 | 9 | 67% | 6 | 3 | 9 | 67% | ||
9 | 555 | PERSON5 | 2 | POS1 | MGR4 | Active | 6 | 3 | 9 | 67% | 6 | 3 | 9 | 67% | ||
10 | 666 | PERSON6 | 3 | POS2 | MGR1 | Active | 3 | 4 | 7 | 43% | 1 | 4 | 5 | 20% | ||
11 | 777 | PERSON7 | 4 | POS4 | MGR3 | Active | 27 | 4 | 31 | 87% | 4 | 4 | 8 | 50% | ||
12 | Total | 62 | 26 | 88 | 70% | 22 | 25 | 47 | 47% | |||||||
MASTER |
Cell Formulas | ||
---|---|---|
Range | Formula | |
G5:G11 | G5 | =INDIRECT("'"&[@Name]&"'!E2") |
H5:H11 | H5 | =INDIRECT("'"&[@Name]&"'!E3") |
I5:I11 | I5 | =INDIRECT("'"&[@Name]&"'!E4") |
J5:J11 | J5 | =INDIRECT("'"&[@Name]&"'!E5") |
K5:K11 | K5 | =INDIRECT("'"&[@Name]&"'!F2") |
L5:L11 | L5 | =INDIRECT("'"&[@Name]&"'!F3") |
M5:M11 | M5 | =INDIRECT("'"&[@Name]&"'!F4") |
N5:N11 | N5 | =INDIRECT("'"&[@Name]&"'!F5") |
G12 | G12 | =SUBTOTAL(109,[Total Worked]) |
H12 | H12 | =SUBTOTAL(109,[Total Not Worked]) |
I12 | I12 | =SUBTOTAL(109,[Total]) |
J12 | J12 | =MASTER[[#Totals],[Total Worked]]/MASTER[[#Totals],[Total]] |
K12 | K12 | =SUBTOTAL(109,[Total Worked (ECD)]) |
L12 | L12 | =SUBTOTAL(109,[Total Not Worked (ECD)]) |
M12 | M12 | =SUBTOTAL(109,[Total (ECD)]) |
N12 | N12 | =MASTER[[#Totals],[Total Worked (ECD)]]/MASTER[[#Totals],[Total (ECD)]] |
FY22 - AVAIL REPORT.xlsm | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | |||
17 | EXCLUDING CALL DUTY | ||||||||||
18 | 1 | 2 | 3 | 4 | 1 | 2 | 3 | 4 | |||
19 | POS1 | 86% | 67% | - | - | 100% | 67% | - | - | ||
20 | POS2 | 39% | - | 43% | - | 21% | - | 20% | - | ||
21 | POS3 | - | 100% | - | - | - | - | - | - | ||
22 | POS4 | - | 67% | - | 87% | - | 67% | - | 50% | ||
23 | 52% | 76% | 43% | 87% | 31% | 67% | 20% | 50% | |||
24 | |||||||||||
25 | |||||||||||
26 | EXCLUDING CALL DUTY | ||||||||||
27 | Total Worked | Total Not Worked | Total | Including CD | Total Worked (ECD) | Total Not Worked (ECD) | Total (ECD) | Excluding CD | |||
28 | MGR1 | 9 | 5 | 14 | 64% | 3 | 4 | 7 | 43% | ||
29 | MGR2 | 13 | 14 | 27 | 48% | 9 | 14 | 23 | 39% | ||
30 | MGR3 | 34 | 4 | 38 | 89% | 4 | 4 | 8 | 50% | ||
31 | MGR4 | 6 | 3 | 9 | 67% | 6 | 3 | 9 | 67% | ||
MASTER |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B19:B22 | B19 | =IFERROR((SUMIFS(MASTER[Total Worked],MASTER[Site],$B$18,MASTER[Class],A19)/(SUMIFS(MASTER[Total],MASTER[Site],$B$18,MASTER[Class],A19))),"-") |
C19:C22 | C19 | =IFERROR((SUMIFS(MASTER[Total Worked],MASTER[Site],$C$18,MASTER[Class],A19)/(SUMIFS(MASTER[Total],MASTER[Site],$C$18,MASTER[Class],A19))),"-") |
D19:D22 | D19 | =IFERROR((SUMIFS(MASTER[Total Worked],MASTER[Site],$D$18,MASTER[Class],A19)/(SUMIFS(MASTER[Total],MASTER[Site],$D$18,MASTER[Class],A19))),"-") |
E19:E22 | E19 | =IFERROR((SUMIFS(MASTER[Total Worked],MASTER[Site],$E$18,MASTER[Class],A19)/(SUMIFS(MASTER[Total],MASTER[Site],$E$18,MASTER[Class],A19))),"-") |
F19:F22 | F19 | =IFERROR((SUMIFS(MASTER[Total Worked (ECD)],MASTER[Site],$F$18,MASTER[Class],A19)/(SUMIFS(MASTER[Total (ECD)],MASTER[Site],$F$18,MASTER[Class],A19))),"-") |
G19:G22 | G19 | =IFERROR((SUMIFS(MASTER[Total Worked (ECD)],MASTER[Site],$G$18,MASTER[Class],A19)/(SUMIFS(MASTER[Total (ECD)],MASTER[Site],$G$18,MASTER[Class],A19))),"-") |
H19:H22 | H19 | =IFERROR((SUMIFS(MASTER[Total Worked (ECD)],MASTER[Site],$H$18,MASTER[Class],A19)/(SUMIFS(MASTER[Total (ECD)],MASTER[Site],$H$18,MASTER[Class],A19))),"-") |
I19:I22 | I19 | =IFERROR((SUMIFS(MASTER[Total Worked (ECD)],MASTER[Site],$I$18,MASTER[Class],A19)/(SUMIFS(MASTER[Total (ECD)],MASTER[Site],$I$18,MASTER[Class],A19))),"-") |
B23 | B23 | =IFERROR((SUMIFS(MASTER[Total Worked],MASTER[Site],$B$18)/(SUMIFS(MASTER[Total],MASTER[Site],$B$18))),"-") |
C23 | C23 | =IFERROR((SUMIFS(MASTER[Total Worked],MASTER[Site],$C$18)/(SUMIFS(MASTER[Total],MASTER[Site],$C$18))),"-") |
D23 | D23 | =IFERROR((SUMIFS(MASTER[Total Worked],MASTER[Site],$D$18)/(SUMIFS(MASTER[Total],MASTER[Site],$D$18))),"-") |
E23 | E23 | =IFERROR((SUMIFS(MASTER[Total Worked],MASTER[Site],$E$18)/(SUMIFS(MASTER[Total],MASTER[Site],$E$18))),"-") |
F23 | F23 | =IFERROR((SUMIFS(MASTER[Total Worked (ECD)],MASTER[Site],$F$18)/(SUMIFS(MASTER[Total (ECD)],MASTER[Site],$F$18))),"-") |
G23 | G23 | =IFERROR((SUMIFS(MASTER[Total Worked (ECD)],MASTER[Site],$G$18)/(SUMIFS(MASTER[Total (ECD)],MASTER[Site],$G$18))),"-") |
H23 | H23 | =IFERROR((SUMIFS(MASTER[Total Worked (ECD)],MASTER[Site],$H$18)/(SUMIFS(MASTER[Total (ECD)],MASTER[Site],$H$18))),"-") |
I23 | I23 | =IFERROR((SUMIFS(MASTER[Total Worked (ECD)],MASTER[Site],$I$18)/(SUMIFS(MASTER[Total (ECD)],MASTER[Site],$I$18))),"-") |
B28 | B28 | =IFERROR(SUMIFS(MASTER[Total Worked],MASTER[Manager],A28),"-") |
C28:C31 | C28 | =IFERROR(SUMIFS(MASTER[Total Not Worked],MASTER[Manager],A28),"-") |
D28:D31 | D28 | =IFERROR(SUMIFS(MASTER[Total],MASTER[Manager],A28),"-") |
E28:E31,I28:I31 | E28 | =IFERROR(B28/D28,"-") |
F28:F31 | F28 | =IFERROR(SUMIFS(MASTER[Total Worked (ECD)],MASTER[Manager],A28),"-") |
G28:G31 | G28 | =IFERROR(SUMIFS(MASTER[Total Not Worked (ECD)],MASTER[Manager],A28),"-") |
H28:H31 | H28 | =IFERROR(SUMIFS(MASTER[Total (ECD)],MASTER[Manager],A28),"-") |
B29:B31 | B29 | =IFERROR(SUMIFS(MASTER[Total Worked],MASTER[Manager],$A29),"-") |
Person1 Tab
FY22 - AVAIL REPORT.xlsm | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | Availability Report: | PERSON1 | INCLUDING Call Duty | EXCLUDING Call Duty | ||||
2 | VruId: | 111 | Total Worked | 6 | 2 | |||
3 | 1 - POS1 | Total Not Worked | 1 | 0 | ||||
4 | Total | 7 | 2 | |||||
5 | Selected Range: | Feb 01, 2021 - Jun 11, 2021 | Availability Percentage | 86% | 100% | |||
6 | ||||||||
7 | Eff Date | Called Date | Callout ID | Description | Response | Outcome | ||
8 | 4/29/2021 15:00 | 4/29/2021 13:33 | 24588 | Planned Overtime callout for: Underground-Craft Planned | Accepted | Credit | ||
9 | 5/1/2021 15:15 | 5/1/2021 15:16 | 24677 | Emergency - Electric callout for: Call Duty-UG CRAFT CALL DUTY CREW | Accepted | Credit | ||
10 | 5/28/2021 23:39 | 5/28/2021 23:41 | 24979 | Emergency - Electric callout for: Call Duty-3RD STAND BY - UG CRAFT CREW | Answering Machine | Charged | ||
11 | 5/29/2021 17:05 | 5/29/2021 17:07 | 25052 | Emergency - Electric callout for: Call Duty-3RD STAND BY - UG CRAFT CREW | Accepted | Credit | ||
12 | 5/31/2021 9:01 | 5/31/2021 9:13 | 25154 | Emergency - Electric callout for: Call Duty-3RD STAND BY - UG CRAFT CREW | Accepted | Credit | ||
13 | 6/1/2021 15:45 | 6/1/2021 15:48 | 25211 | Emergency - Electric callout for: Call Duty-3RD STAND BY - UG CRAFT CREW | Accepted | Credit | ||
14 | 6/5/2021 11:00 | 6/4/2021 11:58 | 25255 | Planned Overtime callout for: Underground-Craft Planned | Accepted | Credit | ||
15 | ||||||||
PERSON1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B1 | B1 | =MASTER!B5 |
B2 | B2 | =MASTER!A5 |
E2 | E2 | =COUNTIF(PERSON1[Outcome],"Credit") |
F2 | F2 | =COUNTIFS(PERSON1[Description],"<>*Call Duty*",PERSON1[Outcome],"Credit") |
E3 | E3 | =COUNTIF(PERSON1[Outcome],"Charged") |
F3 | F3 | =COUNTIFS(PERSON1[Description],"<>*Call Duty*",PERSON1[Outcome],"Charged") |
E4 | E4 | =COUNTIFS(PERSON1[Outcome], "<>*Excused*", PERSON1[Outcome], "*") |
F4 | F4 | =COUNTIFS(PERSON1[Description],"<>*Call Duty*", PERSON1[Outcome],"<>*Excused*",PERSON1[Outcome],"<>") |
E5 | E5 | =IFERROR(E2/E4,"-") |
F5 | F5 | =IFERROR(F2/F4, "-") |
A3 | A3 | =MASTER!C5&" - "&MASTER!D5 |
B5 | B5 | =MASTER!$A$2 |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
D8:D60 | Cell Value | contains "Call Duty" | text | NO |
Person2 Tab
FY22 - AVAIL REPORT.xlsm | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | Availability Report: | PERSON2 | INCLUDING Call Duty | EXCLUDING Call Duty | ||||
2 | VruId: | 222 | Total Worked | 7 | 3 | |||
3 | 1 - POS2 | Total Not Worked | 11 | 11 | ||||
4 | Total | 18 | 14 | |||||
5 | Selected Range: | Feb 01, 2021 - Jun 11, 2021 | Availability Percentage | 39% | 21% | |||
6 | ||||||||
7 | Eff Date | Called Date | Callout ID | Description | Response | Outcome | ||
8 | 2/15/2021 16:35 | 2/15/2021 16:41 | 23835 | Emergency - Electric callout for: ESD-OH Crew 2 C-19 | Telephone - Ring, No Answer | Charged | ||
9 | 2/16/2021 15:14 | 2/16/2021 15:16 | 23867 | Emergency - Electric callout for: ESD-OH Crew 2 C-19 | Declined | Charged | ||
10 | 2/17/2021 15:04 | 2/17/2021 15:08 | 23902 | Emergency - Electric callout for: ESD-OH Crew 2 C-19 | Declined | Charged | ||
11 | 2/18/2021 15:58 | 2/18/2021 16:00 | 23950 | Emergency - Electric callout for: ESD-OH Crew 2 C-19 | Declined | Charged | ||
12 | 3/13/2021 8:45 | 3/13/2021 9:03 | 24153 | Emergency - Electric callout for: Wire Down-Wire Down Tier 1 | Telephone - Ring, No Answer | Charged | ||
13 | 4/23/2021 14:26 | 4/23/2021 14:26 | 24486 | Emergency - Electric callout for: ESD-O/H Foreman - ESD | Declined | Charged | ||
14 | 4/28/2021 22:50 | 4/28/2021 23:29 | 24556 | Emergency - Electric callout for: Wire Down-Wire Down Tier 1 | Telephone - Ring, No Answer | Charged | ||
15 | 4/29/2021 3:55 | 4/29/2021 4:38 | 24561 | Emergency - Electric callout for: ESD-OH Crew RAdams | Accepted | Credit | ||
16 | 4/29/2021 13:56 | 4/29/2021 13:57 | 24595 | Emergency - Electric callout for: ESD-OH Crew RAdams | Declined | Charged | ||
17 | 5/1/2021 12:54 | 5/1/2021 12:55 | 24670 | Emergency - Electric callout for: ESD-OH Crew RAdams | Declined | Charged | ||
18 | 5/1/2021 13:27 | 5/1/2021 13:30 | 24672 | Emergency - Electric callout for: ESD-O/H Foreman - ESD | Max Charges Exceeded | Excused | ||
19 | 5/11/2021 1:31 | 5/11/2021 1:33 | 24772 | Emergency - Electric callout for: ESD-OH Crew RAdams | Telephone - Ring, No Answer | Charged | ||
20 | 5/18/2021 1:01 | 5/18/2021 1:13 | 24836 | Emergency - Electric callout for: Wire Down-Wire Down Tier 1 | Telephone - Ring, No Answer | Charged | ||
21 | 5/28/2021 22:05 | 5/28/2021 22:07 | 24965 | Emergency - Electric callout for: Call Duty-3RD STAND BY - OH CREW | Accepted | Credit | ||
22 | 5/29/2021 16:35 | 5/29/2021 16:36 | 25047 | Emergency - Electric callout for: Call Duty-3RD STAND BY - OH CREW | Accepted | Credit | ||
23 | 5/30/2021 18:55 | 5/30/2021 18:59 | 25140 | Emergency - Electric callout for: ESD-OH Crew RAdams | Accepted | Credit | ||
24 | 5/31/2021 15:59 | 5/31/2021 16:33 | 25170 | Emergency - Electric callout for: Call Duty-3RD STAND BY - OH CREW | Accepted | Credit | ||
25 | 5/31/2021 19:09 | 5/31/2021 19:10 | 25180 | Emergency - Electric callout for: ESD-O/H Foreman - ESD | Working - Emergency Callout | Excused | ||
26 | 6/1/2021 16:32 | 6/1/2021 16:33 | 25214 | Emergency - Electric callout for: Call Duty-3RD STAND BY - OH CREW | Accepted | Credit | ||
27 | 6/5/2021 23:12 | 6/5/2021 23:18 | 25288 | Emergency - Electric callout for: ESD-O/H Foreman - ESD | Working - Emergency Callout | Excused | ||
28 | 6/7/2021 13:50 | 6/7/2021 13:52 | 25312 | Emergency - Electric callout for: ESD-Working 1 | Accepted | Credit | ||
29 | ||||||||
PERSON2 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B1 | B1 | =MASTER!B6 |
B2 | B2 | =MASTER!A6 |
E2 | E2 | =COUNTIF(PERSON2[Outcome],"Credit") |
F2 | F2 | =COUNTIFS(PERSON2[Description],"<>*Call Duty*",PERSON2[Outcome],"Credit") |
E3 | E3 | =COUNTIF(PERSON2[Outcome],"Charged") |
F3 | F3 | =COUNTIFS(PERSON2[Description],"<>*Call Duty*",PERSON2[Outcome],"Charged") |
E4 | E4 | =COUNTIFS(PERSON2[Outcome], "<>*Excused*", PERSON2[Outcome], "*") |
F4 | F4 | =COUNTIFS(PERSON2[Description],"<>*Call Duty*", PERSON2[Outcome],"<>*Excused*",PERSON2[Outcome],"<>") |
E5 | E5 | =IFERROR(E2/E4,"-") |
F5 | F5 | =IFERROR(F2/F4, "-") |
A3 | A3 | =MASTER!C6&" - "&MASTER!D6 |
B5 | B5 | =MASTER!A2 |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
D8:D60 | Cell Value | contains "Call Duty" | text | NO |