Filter Formula Pulls in Blank Cells From Range Of Data Formatted as dates

[Mingalsbe]

Hello,

I've been racking my brain over this last piece of the puzzle. I have read many forums and attempted many solutions but to no avail.

I have a range of Different Dates on one sheet i am trying to pull data ie. Names and dates from using the Filter Function. Currently i have a few different criteria in this filter which seems to work fine but no matter what i do i still end up with some names showing blank dates. I have prioritized one section of dates as the primary selection and my goal is to just not have these names pull over if the Date cell is blank.

I have attempted to adjust the filter function several different ways and played with a lot of the custom date formatting. See formula below. Underlined below is the section i am attempting to eliminate these blank fields.

=FILTER('CTR SECURITY'!D40:P500,('CTR SECURITY'!L40:L500)*('CTR SECURITY'!L40:L500<>"")+('CTR SECURITY'!AD40:AD500="A: Incomplete")+('CTR SECURITY'!AD40:AD500="B: Post Completed")+('CTR SECURITY'!AD40:AD500="C: Completed All Training")+('CTR SECURITY'!AD40:AD500="D: Currently Non-Sworn")+('CTR SECURITY'!AD40:AD500="E: Medical")+('CTR SECURITY'!AD40:AD500="F: Military"))

Any help would be greatly appreciated.

 

[Alex Blakenburg]

Give this a try:

=LET(colDt,'CTR SECURITY'!L40:L500,
colStatus,'CTR SECURITY'!AD40:AD500,
arrStatus,BYROW(colStatus,LAMBDA(row,SUM(--(row={"A: Incomplete","B: Post Completed","C: Completed All Training","D: Currently Non-Sworn","E: Medical","F: Military"})))),
FILTER('CTR SECURITY'!D40:P500,arrStatus*(colDt<>"")))

To make your version work without the additional functions try this:
(the plus are OR and you need to evaluate that first and then multiply the result of that against your date <> "" criteria)

=LET(colStatus,'CTR SECURITY'!AD40:AD500,
FILTER('CTR SECURITY'!D40:P500, ('CTR SECURITY'!L40:L500<>"") *
(
(colStatus="A: Incomplete")+
(colStatus="B: Post Completed")+
(colStatus="C: Completed All Training")+
(colStatus="D: Currently Non-Sworn")+
(colStatus="E: Medical")+
(colStatus="F: Military")))
)

 

[Mingalsbe]

Thank you That seemed to help. I was trying the LET function before but was unsuccessful. This explanation helps outline it in simpler terms.

 

[Alex Blakenburg]

You're welcome. Glad I could help.