Excel 365 - Advanced Filter with Dates Criteria

Hello All,

Need help with figuring out, how to set advanced filter using criteria based on dates.

Objective :- Filter the data from the sheet - "Achievement Tracker" to the sheet - "Dashboard" based on multiple criteria's.

Requirement : The criteria's for filtering the data include - status, target month, completed month. For e.g., when I select the status as "in Progress" and then select the outlook month as Nov'19, then it should filter and show data for all "In Progress" entries with target date as "Nov'19". Likewise, one can filter based on achieved month and all entries which got completed in the selected month/year, should show up on the Dashboard.

Problem : I can filter based on single criteria, i.e. Status. But other criteria's based on month's/year is not working.

Kindly review and advise.

Thanks & Regards,

Shiju Cherian

Hello Shashank,

Thank you for the inputs. I tried updating the headers of the criteria range to match the headers of the raw data and even the dashboard but for some reason, the filtering is still not working.

Below and attached is the snapshot of the Advanced Filter options I am selecting but no output is generated. I filtered for all completed tasks in Nov'19 but no results came up.

Advanced Filter Criteria.JPG

I also made changes to the target and completed month format, so now its in MMM-YY format.

Please review.

Thank You

P.S - The REFRESH button is not functional yet, as I first wanted to sort out the filtering issue and then record the MACRO for it.

Thank you BMV .. the formula was really helpful and I am able to extract the data from the raw data sheet.

Just one query - When I extract "In Progress" based on Target Month, its giving me both the both the "In Progress" & "Completed" for the month of Oct'19.

I wonder why its happening. So I plan to either use target month or completed month in criteria but seems I am not doing it correctly.

Filtered results.JPG

I did try to apply the AND OR logic to the criteria but its not working.

Criteria_Advanced Filter.JPG

Please advise.

Thank You

P.S. - Attached the file you had shared

Hello BMV,

Sir, extremely sorry that I am bugging you so much. But because of my naive knowledge on this topic, I am struggling a bit to apply the logic and formulas you have shared.

E.g. I am trying to filter entries which are "In Progress" and with target date as Oct-19.As advised by you, I moved the formula from AC3 to AC2. Now I applied two options to extract data.

1 - Using Criteria Range as 'Achievement Tracker'!$AA$1:$AC$2 --> Results : No data gets filtered

Attachment 658564


2 - Using Criteria Range as Name Manager - "Criteria", which refers to ='Achievement Tracker'!$AE$1:$AE$2 --> Results : No data filtered

Attachment 658565

Describing the requirement, in case I may have missed any points earlier - The user should be able to extract and filter based on status and completed date or target date. In Progress entries will only have target date, while completed entries may have both target and completed date.

Attached, the last update files, on which I was working for further review and assistance.

Thanks & Regards

Hello,

Another attempt by me at trying to get this working but still stuck with the logic. If you look at the screenshot below, then shouldn't the criteria value change to "TRUE" when conditions are met.

Scenario : Extract is in "In Progress" status and has target date of Oct'19
Attachment 658626

Scenario : Extract is in "Completed" status and has completed date is Nov'19
Attachment 658627

However, raw data contains entries, meeting related conditions.

Attachment 658628

Thanks & Regards.

P.S. - The raw excel is same as attached with previous posts, on this thread.

BMV --> Thank you for your patience and continued support.

Just one more query. So I could probably drag down the formula in column R and then make the entire column as "criteria". But I have a doubt that the formula still address the conditional and multiple criteria's logic.

Like you would note in the excel you had shared, the filter is set as Status = In Progress and Target Month = Oct-19 but if we look at Column R, then its showing as "TRUE" for entry with Target Date = Dec-19. Below is snapshot -

Attachment 658643


When I apply advanced filter by using List Range = Achievement_RawData ; Criteria Range = 'Achievement Tracker'!$R$3:$R$7 ; Copy To = Dashboard!Extract, instead of filtering entry which has status "In Progress" and Target Date as Oct'19, its actually filter all the RAW data, irrespective of status or target date. Below is snapshot -

Attachment 658646

Attachment 658647

Please advise, how I can use to filter data based on drop-downs selected.

Thanks & Regards

it's my fault. I have corrected post and file.

So I could probably drag down the formula in column R and then make the entire column as "criteria"

Yes you could, but advanced filter do it based one two cells.

Originally Posted by BMV

it's my fault. I have corrected post and file.
Yes you could, but advanced filter do it based one two cells.

Ok Sir .. but in this case doesn't look like it will solve my problem. I tried using advanced filter again and choose the Criteria Range as R3:R7 but it doesn't give me the correct extract i.e. Status - "In Progress" & Target Date = "Dec-19".

If Advanced Filter has constraints, then any other excel solution that you can suggest please.

I am just mentioning my requirement again. I was hoping that advanced filter will be a solution but seems we have many challenges.

Attachment 658666

Thank you for your continued support and guidance. This now works fine.