Filter for multiple date ranges

[jjulien03]

I'm building a template and in this template I have multiple fields for different parameters that will need to be filtered. One filter is for time and I'm having trouble figuring out how to filter multiple time periods i.e. filter out data from times between 7-9AM and 1-2PM. I'm able to filter for one period but have not been able to figure how to do more than one without using VBA, because I don't know how to use VBA. Any help would be appreciated and if more information is needed to help solve this, please let me know.
Thanks

[#2]

do you have a sample file

[#3]

@Yea_So Yes, A2 on the filtered data tab is where the formula is located. Cells C12-C15 on the Filters&Results tab are the input cells I'd like to use. Thanks for taking a look

[#4]

Hi @jjulien03 

Advanced filtering disappeared on my system i'll have to wait for it to come back whenever uncle MS returns the feature

[#5]

Hi @jjulien03 

What I created for your filter problem is not a formula based solution, but rather a power query solution.

Step 1. I converted your Raw Data Adjusted into a table and did a power query to it:

then I created a parameter table to connect your Filters&Results sheet where you enter your search parameters and called it ParamConnector.

then I did a power query on the Parameter table

and made three references to it and named them:

Search1Results, Search2Results, and Search3Results

On Search1Results I did the following:

1 I added a custom column then entered M language to select the rows as prescribed in the 1st Shade Omissions Period start and end dates:

the M Language: 

Table.SelectRows(
RDA,
(InnerTable) => InnerTable[Date] >= [SD] and InnerTable[Date] <= [ED] and InnerTable[Time] >= [R1S] and InnerTable[Time] <= [R1E]
)

SD=Start Date

ED=End Date

R1S= Start Time Range number 1 in Filter&Results Sheet 

R1E= End Time Range number 1 in Filter&Results Sheet

Repeat and Rinse for the other 2 Search2Results and Search3Results making sure the variable references are referening the correct parameters. (Check it in the Added Custom step in the Query Settings navigation panel by clicking the gear setting beside it)

After all three search results have been done, I then did a Merge query >Append on all three parameter queries Search1Result, Search2Results, and Search3Results.

then I Closed & Loaded to the worksheet and named it 3VectorSearchResults sheet:

Here is the file => My Template Neer Cap Test  

When you enter new parameters in your Filter&Results Sheet, press ctrl+alt+F5 to refresh the query.

if you have any questions please feel free to reach out.

cheers

[#6]

@jjulien03 

I think there were errors in the way in which elements of the filter criterion were formulated.  Additional parentheses were needed.

=FILTER(siteTime#:finalColumn,
     (Date#<=endDate)*(Date#>=startDate)
    *((Time#>eT₁)+(Time#<sT₁))
    *(POA#>=minIrr)
    *((Time#>eT₂)+(Time#<sT₂)))

I converted the raw data to an Excel table and started building the derived data as dynamic arrays with a defined name applied to the anchor cell for reference purposes.