Filter Report based on Dates

alm395

alm395

New Member
Joined
Apr 23, 2018
Messages
39
Office Version
  1. 365
Platform
  1. 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!

FY22 - AVAIL REPORT.xlsm
ABCDEFGHIJKLMN
1Availability Report by IndividualSTART DATE:4/23/2021 00:00:00
2Feb 01, 2021 - Jun 11, 2021END DATE:4/24/2021 00:00:00
3EXCLUDING CALL DUTY
4VruIDNameSiteClassManagerStatusTotal WorkedTotal Not WorkedTotalIncluding CDTotal Worked (ECD)Total Not Worked (ECD)Total (ECD)Excluding CD
5111PERSON11POS1MGR1Active61786%202100%
6222PERSON21POS2MGR2Active7111839%3111421%
7333PERSON32POS3MGR3Active707100%000-
8444PERSON52POS4MGR2Active63967%63967%
9555PERSON52POS1MGR4Active63967%63967%
10666PERSON63POS2MGR1Active34743%14520%
11777PERSON74POS4MGR3Active2743187%44850%
12Total62268870%22254747%
MASTER
Cell Formulas
RangeFormula
G5:G11G5=INDIRECT("'"&[@Name]&"'!E2")
H5:H11H5=INDIRECT("'"&[@Name]&"'!E3")
I5:I11I5=INDIRECT("'"&[@Name]&"'!E4")
J5:J11J5=INDIRECT("'"&[@Name]&"'!E5")
K5:K11K5=INDIRECT("'"&[@Name]&"'!F2")
L5:L11L5=INDIRECT("'"&[@Name]&"'!F3")
M5:M11M5=INDIRECT("'"&[@Name]&"'!F4")
N5:N11N5=INDIRECT("'"&[@Name]&"'!F5")
G12G12=SUBTOTAL(109,[Total Worked])
H12H12=SUBTOTAL(109,[Total Not Worked])
I12I12=SUBTOTAL(109,[Total])
J12J12=MASTER[[#Totals],[Total Worked]]/MASTER[[#Totals],[Total]]
K12K12=SUBTOTAL(109,[Total Worked (ECD)])
L12L12=SUBTOTAL(109,[Total Not Worked (ECD)])
M12M12=SUBTOTAL(109,[Total (ECD)])
N12N12=MASTER[[#Totals],[Total Worked (ECD)]]/MASTER[[#Totals],[Total (ECD)]]


FY22 - AVAIL REPORT.xlsm
ABCDEFGHI
17EXCLUDING CALL DUTY
1812341234
19POS186%67%--100%67%--
20POS239%-43%-21%-20%-
21POS3-100%------
22POS4-67%-87%-67%-50%
2352%76%43%87%31%67%20%50%
24
25
26EXCLUDING CALL DUTY
27Total WorkedTotal Not WorkedTotalIncluding CDTotal Worked (ECD)Total Not Worked (ECD)Total (ECD)Excluding CD
28MGR1951464%34743%
29MGR213142748%9142339%
30MGR33443889%44850%
31MGR463967%63967%
MASTER
Cell Formulas
RangeFormula
B19:B22B19=IFERROR((SUMIFS(MASTER[Total Worked],MASTER[Site],$B$18,MASTER[Class],A19)/(SUMIFS(MASTER[Total],MASTER[Site],$B$18,MASTER[Class],A19))),"-")
C19:C22C19=IFERROR((SUMIFS(MASTER[Total Worked],MASTER[Site],$C$18,MASTER[Class],A19)/(SUMIFS(MASTER[Total],MASTER[Site],$C$18,MASTER[Class],A19))),"-")
D19:D22D19=IFERROR((SUMIFS(MASTER[Total Worked],MASTER[Site],$D$18,MASTER[Class],A19)/(SUMIFS(MASTER[Total],MASTER[Site],$D$18,MASTER[Class],A19))),"-")
E19:E22E19=IFERROR((SUMIFS(MASTER[Total Worked],MASTER[Site],$E$18,MASTER[Class],A19)/(SUMIFS(MASTER[Total],MASTER[Site],$E$18,MASTER[Class],A19))),"-")
F19:F22F19=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:G22G19=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:H22H19=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:I22I19=IFERROR((SUMIFS(MASTER[Total Worked (ECD)],MASTER[Site],$I$18,MASTER[Class],A19)/(SUMIFS(MASTER[Total (ECD)],MASTER[Site],$I$18,MASTER[Class],A19))),"-")
B23B23=IFERROR((SUMIFS(MASTER[Total Worked],MASTER[Site],$B$18)/(SUMIFS(MASTER[Total],MASTER[Site],$B$18))),"-")
C23C23=IFERROR((SUMIFS(MASTER[Total Worked],MASTER[Site],$C$18)/(SUMIFS(MASTER[Total],MASTER[Site],$C$18))),"-")
D23D23=IFERROR((SUMIFS(MASTER[Total Worked],MASTER[Site],$D$18)/(SUMIFS(MASTER[Total],MASTER[Site],$D$18))),"-")
E23E23=IFERROR((SUMIFS(MASTER[Total Worked],MASTER[Site],$E$18)/(SUMIFS(MASTER[Total],MASTER[Site],$E$18))),"-")
F23F23=IFERROR((SUMIFS(MASTER[Total Worked (ECD)],MASTER[Site],$F$18)/(SUMIFS(MASTER[Total (ECD)],MASTER[Site],$F$18))),"-")
G23G23=IFERROR((SUMIFS(MASTER[Total Worked (ECD)],MASTER[Site],$G$18)/(SUMIFS(MASTER[Total (ECD)],MASTER[Site],$G$18))),"-")
H23H23=IFERROR((SUMIFS(MASTER[Total Worked (ECD)],MASTER[Site],$H$18)/(SUMIFS(MASTER[Total (ECD)],MASTER[Site],$H$18))),"-")
I23I23=IFERROR((SUMIFS(MASTER[Total Worked (ECD)],MASTER[Site],$I$18)/(SUMIFS(MASTER[Total (ECD)],MASTER[Site],$I$18))),"-")
B28B28=IFERROR(SUMIFS(MASTER[Total Worked],MASTER[Manager],A28),"-")
C28:C31C28=IFERROR(SUMIFS(MASTER[Total Not Worked],MASTER[Manager],A28),"-")
D28:D31D28=IFERROR(SUMIFS(MASTER[Total],MASTER[Manager],A28),"-")
E28:E31,I28:I31E28=IFERROR(B28/D28,"-")
F28:F31F28=IFERROR(SUMIFS(MASTER[Total Worked (ECD)],MASTER[Manager],A28),"-")
G28:G31G28=IFERROR(SUMIFS(MASTER[Total Not Worked (ECD)],MASTER[Manager],A28),"-")
H28:H31H28=IFERROR(SUMIFS(MASTER[Total (ECD)],MASTER[Manager],A28),"-")
B29:B31B29=IFERROR(SUMIFS(MASTER[Total Worked],MASTER[Manager],$A29),"-")


Person1 Tab
FY22 - AVAIL REPORT.xlsm
ABCDEF
1Availability Report:PERSON1INCLUDING Call DutyEXCLUDING Call Duty
2VruId: 111Total Worked62
31 - POS1Total Not Worked10
4Total72
5Selected Range: Feb 01, 2021 - Jun 11, 2021Availability Percentage86%100%
6
7Eff DateCalled DateCallout IDDescriptionResponseOutcome
84/29/2021 15:004/29/2021 13:3324588Planned Overtime callout for: Underground-Craft PlannedAcceptedCredit
95/1/2021 15:155/1/2021 15:1624677Emergency - Electric callout for: Call Duty-UG CRAFT CALL DUTY CREWAcceptedCredit
105/28/2021 23:395/28/2021 23:4124979Emergency - Electric callout for: Call Duty-3RD STAND BY - UG CRAFT CREWAnswering MachineCharged
115/29/2021 17:055/29/2021 17:0725052Emergency - Electric callout for: Call Duty-3RD STAND BY - UG CRAFT CREWAcceptedCredit
125/31/2021 9:015/31/2021 9:1325154Emergency - Electric callout for: Call Duty-3RD STAND BY - UG CRAFT CREWAcceptedCredit
136/1/2021 15:456/1/2021 15:4825211Emergency - Electric callout for: Call Duty-3RD STAND BY - UG CRAFT CREWAcceptedCredit
146/5/2021 11:006/4/2021 11:5825255Planned Overtime callout for: Underground-Craft PlannedAcceptedCredit
15
PERSON1
Cell Formulas
RangeFormula
B1B1=MASTER!B5
B2B2=MASTER!A5
E2E2=COUNTIF(PERSON1[Outcome],"Credit")
F2F2=COUNTIFS(PERSON1[Description],"<>*Call Duty*",PERSON1[Outcome],"Credit")
E3E3=COUNTIF(PERSON1[Outcome],"Charged")
F3F3=COUNTIFS(PERSON1[Description],"<>*Call Duty*",PERSON1[Outcome],"Charged")
E4E4=COUNTIFS(PERSON1[Outcome], "<>*Excused*", PERSON1[Outcome], "*")
F4F4=COUNTIFS(PERSON1[Description],"<>*Call Duty*", PERSON1[Outcome],"<>*Excused*",PERSON1[Outcome],"<>")
E5E5=IFERROR(E2/E4,"-")
F5F5=IFERROR(F2/F4, "-")
A3A3=MASTER!C5&" - "&MASTER!D5
B5B5=MASTER!$A$2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D8:D60Cell Valuecontains "Call Duty"textNO


Person2 Tab
FY22 - AVAIL REPORT.xlsm
ABCDEF
1Availability Report:PERSON2INCLUDING Call DutyEXCLUDING Call Duty
2VruId: 222Total Worked73
31 - POS2Total Not Worked1111
4Total1814
5Selected Range: Feb 01, 2021 - Jun 11, 2021Availability Percentage39%21%
6
7Eff DateCalled DateCallout IDDescriptionResponseOutcome
82/15/2021 16:352/15/2021 16:4123835Emergency - Electric callout for: ESD-OH Crew 2 C-19Telephone - Ring, No AnswerCharged
92/16/2021 15:142/16/2021 15:1623867Emergency - Electric callout for: ESD-OH Crew 2 C-19DeclinedCharged
102/17/2021 15:042/17/2021 15:0823902Emergency - Electric callout for: ESD-OH Crew 2 C-19DeclinedCharged
112/18/2021 15:582/18/2021 16:0023950Emergency - Electric callout for: ESD-OH Crew 2 C-19DeclinedCharged
123/13/2021 8:453/13/2021 9:0324153Emergency - Electric callout for: Wire Down-Wire Down Tier 1Telephone - Ring, No AnswerCharged
134/23/2021 14:264/23/2021 14:2624486Emergency - Electric callout for: ESD-O/H Foreman - ESDDeclinedCharged
144/28/2021 22:504/28/2021 23:2924556Emergency - Electric callout for: Wire Down-Wire Down Tier 1Telephone - Ring, No AnswerCharged
154/29/2021 3:554/29/2021 4:3824561Emergency - Electric callout for: ESD-OH Crew RAdamsAcceptedCredit
164/29/2021 13:564/29/2021 13:5724595Emergency - Electric callout for: ESD-OH Crew RAdamsDeclinedCharged
175/1/2021 12:545/1/2021 12:5524670Emergency - Electric callout for: ESD-OH Crew RAdamsDeclinedCharged
185/1/2021 13:275/1/2021 13:3024672Emergency - Electric callout for: ESD-O/H Foreman - ESDMax Charges ExceededExcused
195/11/2021 1:315/11/2021 1:3324772Emergency - Electric callout for: ESD-OH Crew RAdamsTelephone - Ring, No AnswerCharged
205/18/2021 1:015/18/2021 1:1324836Emergency - Electric callout for: Wire Down-Wire Down Tier 1Telephone - Ring, No AnswerCharged
215/28/2021 22:055/28/2021 22:0724965Emergency - Electric callout for: Call Duty-3RD STAND BY - OH CREWAcceptedCredit
225/29/2021 16:355/29/2021 16:3625047Emergency - Electric callout for: Call Duty-3RD STAND BY - OH CREWAcceptedCredit
235/30/2021 18:555/30/2021 18:5925140Emergency - Electric callout for: ESD-OH Crew RAdamsAcceptedCredit
245/31/2021 15:595/31/2021 16:3325170Emergency - Electric callout for: Call Duty-3RD STAND BY - OH CREWAcceptedCredit
255/31/2021 19:095/31/2021 19:1025180Emergency - Electric callout for: ESD-O/H Foreman - ESDWorking - Emergency CalloutExcused
266/1/2021 16:326/1/2021 16:3325214Emergency - Electric callout for: Call Duty-3RD STAND BY - OH CREWAcceptedCredit
276/5/2021 23:126/5/2021 23:1825288Emergency - Electric callout for: ESD-O/H Foreman - ESDWorking - Emergency CalloutExcused
286/7/2021 13:506/7/2021 13:5225312Emergency - Electric callout for: ESD-Working 1AcceptedCredit
29
PERSON2
Cell Formulas
RangeFormula
B1B1=MASTER!B6
B2B2=MASTER!A6
E2E2=COUNTIF(PERSON2[Outcome],"Credit")
F2F2=COUNTIFS(PERSON2[Description],"<>*Call Duty*",PERSON2[Outcome],"Credit")
E3E3=COUNTIF(PERSON2[Outcome],"Charged")
F3F3=COUNTIFS(PERSON2[Description],"<>*Call Duty*",PERSON2[Outcome],"Charged")
E4E4=COUNTIFS(PERSON2[Outcome], "<>*Excused*", PERSON2[Outcome], "*")
F4F4=COUNTIFS(PERSON2[Description],"<>*Call Duty*", PERSON2[Outcome],"<>*Excused*",PERSON2[Outcome],"<>")
E5E5=IFERROR(E2/E4,"-")
F5F5=IFERROR(F2/F4, "-")
A3A3=MASTER!C6&" - "&MASTER!D6
B5B5=MASTER!A2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D8:D60Cell Valuecontains "Call Duty"textNO
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
alm395 said:
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.
Click to expand...
This is achievable using vba. But for that to work, your data set should have a column containing dates. Then we can use filter and feed in the criteria as Start Date and End Date. Your Person1 and Person2 worksheets contains dates columns but Master Sheet doesn't.

You can test following code with person1 worksheet, since it already has date column.

VBA Code: 
Sub alm395()
Dim stdate As Date
Dim eddate As Date
lr = Sheets("Person1").Cells(Rows.Count, 1).End(xlUp).Row

stdate = Sheets("Person1").Range("J2")
eddate = Sheets("Person1").Range("j3")

Sheets("Person1").Range("A8:f" & lr).AutoFilter 1, ">=" & stdate, xlAnd, "<=" & eddate
End Sub


hth....
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
How to fix Filter formula #SPILL! error by change to use the VBA
Replies
2
Views
600
alantse2010
A
D
  • Question
List Generator with index match within date range
Replies
2
Views
448
Peter_SSs
Peter_SSs
M
  • Question
Auto adjust row heights based on specific column
Replies
1
Views
160
XLfriend777
XLfriend777
C
  • Question
Mutiple criteria filtering to separate 2NF table (nested FILTER over FILTER formula?)
Replies
1
Views
342
Fluff
Fluff
MrDB4Excel
  • Question
Insert Value Based on Multiple Criteria
Replies
9
Views
892
jtakw
jtakw

Forum statistics

Threads
1,214,948
Messages
6,122,420
Members
449,083
Latest member
Ava19

Share this page

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
Back
Top