+ Reply to Thread
Results 1 to 14 of 14

Excel 365 - Advanced Filter with Dates Criteria

  1. #1
    Registered User
    Join Date
    09-14-2015
    Location
    New Delhi
    MS-Off Ver
    2013
    Posts
    35

    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
    Attached Files Attached Files

  2. #2
    Forum Contributor shank_mis's Avatar
    Join Date
    09-08-2018
    Location
    Delhi
    MS-Off Ver
    2010
    Posts
    128

    Re: Excel 365 - Advanced Filter with Dates Criteria

    I think your criteria range is not properly set up.
    Heading's name in Criteria range should be same as data range.
    Criteria for filtering should come under that particular heading.
    This will definitely work.
    Shashank Mishra
    Please hit "Add Reputation" Button if you liked the answer.

  3. #3
    Registered User
    Join Date
    09-14-2015
    Location
    New Delhi
    MS-Off Ver
    2013
    Posts
    35

    Re: Excel 365 - Advanced Filter with Dates Criteria

    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.
    Attached Files Attached Files

  4. #4
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,327

    Re: Excel 365 - Advanced Filter with Dates Criteria

    Hi
    1. In spite of you see Nov-19 in the cell value of 11/30/2019 as example and it is not equal 11/1/2019 which you use in the dropdown box. So you must change data in source table
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    or do it in the filter range
    2. If You use "" in the criteria it is not blank but 0 for filter


    Look at the picture. Criteria range is green.
    17-01-2020 10-29-16.jpg
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by BMV; 01-17-2020 at 04:10 AM.

  5. #5
    Registered User
    Join Date
    09-14-2015
    Location
    New Delhi
    MS-Off Ver
    2013
    Posts
    35

    Re: Excel 365 - Advanced Filter with Dates Criteria

    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
    Attached Files Attached Files

  6. #6
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,327

    Re: Excel 365 - Advanced Filter with Dates Criteria

    i missed that one criteria in the 3rd row and now it's always blank
    then =AND(OR($AA$2="";I3=$AA$2);OR($AB$2="";EOMONTH(G3;-1)+1=$AB$2);OR($AC$2="";EOMONTH(F3;-1)+1=$AC$3))
    or move your formula from AC3 to AC2.
    Last edited by BMV; 01-17-2020 at 11:26 AM.

  7. #7
    Registered User
    Join Date
    09-14-2015
    Location
    New Delhi
    MS-Off Ver
    2013
    Posts
    35

    Re: Excel 365 - Advanced Filter with Dates Criteria

    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
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    09-14-2015
    Location
    New Delhi
    MS-Off Ver
    2013
    Posts
    35

    Re: Excel 365 - Advanced Filter with Dates Criteria

    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.

  9. #9
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,327

    Re: Excel 365 - Advanced Filter with Dates Criteria

    cherias. There was two problems .
    1. blank cell is equal 0 and this date cannot be use for EOMANTH IFERROR help
    2. I see you try to use OR condition.
    then
    =AND(OR($AA$2="";I3=$AA$2);OR($AB$2="";IFERROR(EOMONTH(G3;-1)+1;0)=$AB$2);OR($AC$2="";IFERROR(EOMONTH(F3;-1)+1;0)=$AC$2))

    Look at the column R. it's sowing how it is working for each row but not necessary for filter.
    Attached Files Attached Files
    Last edited by BMV; 01-20-2020 at 07:07 AM.

  10. #10
    Registered User
    Join Date
    09-14-2015
    Location
    New Delhi
    MS-Off Ver
    2013
    Posts
    35

    Re: Excel 365 - Advanced Filter with Dates Criteria

    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

  11. #11
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,327

    Re: Excel 365 - Advanced Filter with Dates Criteria

    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.

  12. #12
    Registered User
    Join Date
    09-14-2015
    Location
    New Delhi
    MS-Off Ver
    2013
    Posts
    35

    Re: Excel 365 - Advanced Filter with Dates Criteria

    Quote Originally Posted by BMV View Post
    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

  13. #13
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,327

    Re: Excel 365 - Advanced Filter with Dates Criteria

    cheria, it is not easy for me to help yor dyring local working hours :-) I have change formulas in R but not copy it to AE2.
    Look last file. It should be ok and advanced filter was applied here.
    Attached Files Attached Files
    Last edited by BMV; 01-20-2020 at 01:09 PM.

  14. #14
    Registered User
    Join Date
    09-14-2015
    Location
    New Delhi
    MS-Off Ver
    2013
    Posts
    35

    Re: Excel 365 - Advanced Filter with Dates Criteria

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Excel VBA advanced filter criteria range does not work
    By Distrooooy in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-15-2017, 03:30 AM
  2. Replies: 0
    Last Post: 08-04-2017, 02:39 AM
  3. [SOLVED] Advanced filter returning all dates instead of those in criteria range, or error
    By nemmy in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-03-2016, 02:45 PM
  4. Advanced Filter : One Criteria Cell Breaks the Filter - Returns Only Headers
    By PaulGW in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-28-2014, 10:06 AM
  5. excel 2010 - reasons why advanced filter returns ALL ROWS and not criteria set?
    By wyattea in forum Excel Programming / VBA / Macros
    Replies: 33
    Last Post: 09-13-2013, 08:38 PM
  6. Setting criteria between two dates (advanced filter)
    By Chrilliams in forum Excel General
    Replies: 0
    Last Post: 02-08-2011, 12:07 AM
  7. [SOLVED] Wildcards in Advanced filter criteria don't work with Excel 2003
    By Inconceivable in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-14-2005, 10:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1