Filter function using "or" for a date column (blank or >= 01/01/2023)

Lisa116

New Member
Joined
Jul 19, 2019
Messages
18
Office Version
  1. 365
Platform
  1. Windows
I am not sure how to set up the formula to include blanks or >= 01/01/2023 from the same Term Date column.
I am hopeful that this is a quick fix and that someone will shed some light on it. Currently, the formula looks like this:
FILTER(Activity[[SLSNUM]:[Active]],(Activity[Office Number]=O1)*(Activity[Term Date]>=DATE(2023,1,1))*(Activity[Tier]>=0.25),"")

I need to incorporate the rows where there are blanks in the Term Date.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
How about
Excel Formula:
=FILTER(Activity[[SLSNUM]:[Active]],(Activity[Office Number]=O1)*(Activity[Tier]>=0.25)*((Activity[Term Date]>=DATE(2023,1,1))+(Activity[Term Date]="")),"")
 
Upvote 0
Solution
try this:
Excel Formula:
=FILTER(Activity[[SLSNUM]:[Active]],(Activity[Office Number]="O1")*(or(Activity[Term Date]>=DATE(2023,1,1),Activity[Term Date]=""))*(Activity[Tier]>=0.25),"")
 
Upvote 0
You cannot use OR like that in an array
 
Upvote 0
Fluff,
Thank you so much! I appreciate it. Worked perfect. :-)
Question on the date cells that are blank, is there a way to have the filter populate the blank date cells as blank and not 1/0/1900?
 
Upvote 0
Try
Excel Formula:
=LET(f,FILTER(Activity[[SLSNUM]:[Active]],(Activity[Office Number]=O1)*(Activity[Tier]>=0.25)*((Activity[Term Date]>=DATE(2023,1,1))+(Activity[Term Date]="")),""),IF(f="","",f))
 
Upvote 0
Fluff,
Thank you so much! I appreciate it. Worked perfect. :)
The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top