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

Mingalsbe

New Member
Joined
Sep 23, 2023
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Give this a try:

Excel Formula:
=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)
Excel Formula:
=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")))
)
 
Last edited:
Upvote 0
Thank you That seemed to help. I was trying the LET function before but was unsuccessful. This explanation helps outline it in simpler terms.
 
Upvote 0

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

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