Using array formula to pull filtered data not working

Question

Hi all,

I have a list of projects with their start/end dates. What I want is to pull through a list of those projects which are filtered and have an end date (i.e. not paused). In the image below, all rows highlighted in green should be pulled to the right. I need this list to be dynamic as the current date changes, and as projects are paused/restarted.

I've used an if/small array formula to identify end dates that are greater than or equal to today. The first time I tried this it worked for some rows, but struggled to pull all rows through. I simplified the data for testing (see image attached) and it looks like it's pulling random rows through now.

Can someone check what I've done wrong and offer some advice as I'm running out of ideas? Thanks in advance!

Answer 1

The following formula doesn't need column F.

In G3 as an array formula confirmed with Ctrl+Shift+Enter:

=IFERROR(INDEX($B$3:$B$17,SMALL(IF(ISNUMBER($D$3:$D$17)*($D$3:$D$17>=TODAY()),ROW($B$3:$B$17)-MIN(ROW($B$3:$B$17))+1),ROWS($F$3:$F3))),"")

Fill down to G17.

Answer 2

Hi Ravi, this is close to what I want. The only difference being that I want the formulae to skip any FALSE values and to check the next row.

Essentially, a formula that filters values based on the date (and continues filtering as the date changes) to show a snapshot of a larger dataset.

Answer 3

Hi James,

Greetings for the day!

I'm glad to help you regarding the query with excel date formula.

I think you are trying to copy the project which has end date today or future date. Please try with following formula.

=IF(ISNUMBER(D3),(IF(D3>=TODAY(),B3,FALSE)),D3)

Project Start Date End date Project
Project1 18/01/2021 7/11/2022 Project1
Project2 18/01/2022 7/11/2022 Project2
Project3 18/01/2023 7/10/2022 FALSE
Project4 18/01/2024 7/12/2022 Project4
Project5 18/01/2025 No Date No Date
Project6 18/01/2026 7/12/2022 Project6
Project7 18/01/2027 7/8/2022 FALSE
Project8 18/01/2028 7/12/2022 Project8
Project9 18/01/2029 7/13/2022 Project9
Project10 18/01/2030 No Date No Date
Project11 18/01/2031 7/13/2022 Project11
Project12 18/01/2032 7/6/2022 FALSE
Project13 18/01/2033 7/13/2022 Project13
Project14 18/01/2034 7/12/2022 Project14
Project15 18/01/2035 7/13/2022 Project15

Below link is useful for IsDate comparison.
https://answers.microsoft.com/en-us/msoffice/forum/all/formula-to-check-if-it-is-a-date/39c833d6-94d3-4c83-b3f6-117a5fd20344

Hope this information helps. Please feel free to get back if you have any questions.

Thank you!

Answer 4

Office 2019 sadly, otherwise this would be a lifesaver haha

Answer 5

Do you have Microsoft 365 or Office 2021? If so, clear G3:G17, then enter the following formula in G3:

=FILTER(B3:B17,ISNUMBER(D3:D17)*(D3:D17>=TODAY()),"None")