Forum Discussion
Return cells within a specific date range (today-6 weeks ahead)
Hello,
I have made my own "search bar" in excel with the help of the formulas: Filter, Isnumber and Search.
This gives me a dynamic array where it will always update according to my master sheet.
But I have the need to search find cells that fall within a specific date range, precisely today-6 weeks ahead.
Is there a way for me to be able to find these cells that fall within this timeframe? Where it will also be dynamic and show the whole rows as well.
Please help.
Thank you in advance.
4 Replies
=FILTER(A2:G1000,(A2:A1000>=TODAY())*(A2:A1000<=TODAY()+42))
This formula returns the intended result in the attached sample file if i correctly understand what you want to do.
OliverScheurich
Hello Oliver,
Thank you for your answer.
You have indeed understood correctly and thank you for showing that it can be done.
The only difference is that the column I have the dates in are not in colomn A, but rather colomn J.
Can it still be done?
See my current formula below:
=FILTER(Orders;ISNUMBER(SEARCH(D3;Orders[ORDRE]))+ISNUMBER(SEARCH(D3;Orders[KUNDE]))+ISNUMBER(SEARCH(D3;Orders[[Spærret ]]))+ISNUMBER(SEARCH(D3;Orders[[ETD HEDENSTED ]]));"No Match")
Is there any chance you might be able to help me plug your formula into mine above? I am not sure where I should put it.
Thank you in advance.
I am looking forward to your answer.Hello Nicolas94h,
you are welcome. I've made up a sample table with your data and hope this formula works for you.
=FILTER(Orders,
(Orders[Date]>=TODAY())*
(Orders[Date]<=TODAY()+42)*(ISNUMBER(SEARCH(D3,Orders[ORDRE]))+ISNUMBER(SEARCH(D3,Orders[KUNDE]))+ISNUMBER(SEARCH(D3,Orders[[Spærret ]]))+ISNUMBER(SEARCH(D3,Orders[[ETD HEDENSTED ]]))),
"No Match")
