none
Need formula using advanced filter RRS feed

  • Question

  • Hi,

    This is fairly an easy question. I created an advanced filter with a relatively huge data, now i need to setup a criteria to filter and pull info for data that falls within next 60 days. So when i enter todays date and search it automatically filters items that fall within next 60 days. Just need the formula to put in criteria.

    Thank you!


    Pete

    Saturday, January 14, 2017 5:00 AM

Answers

  • y example uses my regional date format which is d/m/y so if not your regional format then edit where required to suit your regional date format.

    When using Advanced filter I like to insert a few lines at the top of the worksheet  for the Criteria and then freeze the panes so that they criteria etc always remains visible.There must be at least one blank row between the criteria and the data to be filtered.

    Set up your worksheet something like the below screen capture where you can enter the minimum and max date. In your case the maximum date can be a formula using minimum date plus 60.

    You then need columns with headers that match the header of the column containing the dates. In the example "Date". The criteria headers must exactly match the header on the column of data to be filtered.

    In the example enter the minimum date in cell D2 and then in cell E2 enter the formula    =D2+60

    In cell A2 enter the following formula (Edit the date format if required)

    =">=" & TEXT(D2,"dd/mm/yyyy")

    In cell B2 enter the following formula (Edit the date format if required)

    ="<=" & TEXT(E2,"dd/mm/yyyy")

    Now you can use advanced filter with the range $A$1:$B$2 as the Criteria range.

    Note that in cells A2 and B2, if you prefer, you can use the date format as follows and it still works.

     =">=" & TEXT(D2,"dd mmm yyyy")

    Note that I have edited this post and uploaded a new screen capture. The previous screen capture showed Formula in B2 instead of E2.


    Regards, OssieMac


    • Proposed as answer by Asadulla JavedEditor Saturday, January 14, 2017 7:42 AM
    • Edited by OssieMac Saturday, January 14, 2017 8:43 AM
    • Marked as answer by Pete198 Saturday, January 14, 2017 1:38 PM
    Saturday, January 14, 2017 7:24 AM