Advanced Filter

Question

Hello.I created a filter that has 2 conditions.&nbsp; 1.&nbsp; If the Ref Cert or the SK Cert is YES, bit should appear.&nbsp; The problem is that if the columns with Ref Exp or SK Exp is blank, then it gives me a 0 or if it does have a date, then it gives me the number equivalent.&nbsp; The original table, it does have dates.&nbsp; I tried to format the columns so it would give me an actual date, but it doesn't work.&nbsp; It only works for the blank cells.This is the actual formula.=FILTER(C3:N500,((H3:H500)="YES")+((J3:J500)="YES"))&amp;IF(ISBLANK(I3),"",I3)Array is C3 : N500 (This is the table where is information is coming from)Ref Cert is H3 : H500SK Cert is J3 : J500This is what the finish product looks like.&nbsp;

m_tarler · Answer

the result of your IF statement is acting on only I3 and concatenated (&) to the output forcing all the output to be text.. maybe try this:

=LET(out, FILTER(C3:N500,((H3:H500)="YES")+((J3:J500)="YES")), IF(out=0,"",out))

kidd_ip · Answer

Try date format as well, Open Settings &gt; Format Cells &gt; Number &gt; Date, then choose a date type (e.g. MM/DD/YYYY) for columns Ref Exp and Sk Exp.

jamony · Answer

The FILTER criteria are fine; the trailing concatenation is causing the result problem. In Excel, dates are stored as serial numbers, and the &amp; operator converts values to text. Your IF also checks only I3, then appends that single result to every cell in the spilled array, so formatting cannot reliably restore the dates. Start with =FILTER(C3:N500,((H3:H500="YES")+(J3:J500="YES"))&gt;0,""). Then format the spilled Ref Exp and SK Exp columns as Date. If source blanks are displayed as zero, use =LET(x,FILTER(C3:N500,((H3:H500="YES")+(J3:J500="YES"))&gt;0,""),IF(x="","",x)). That preserves the filtered two-dimensional shape and substitutes an empty string wherever the returned source cell is blank. Do not append I3 to the array. Also confirm that the expiration cells contain real Excel dates rather than imported text by testing one with ISNUMBER. If it returns FALSE, convert that source column to dates before filtering. Using an Excel Table with structured references will also let the formula expand automatically.

olufemi7 · Answer

Hello BaltaD​,The FILTER criteria is fine. The issue is the &amp;IF(ISBLANK(I3),"",I3) at the end.The &amp; converts the result to text, so Excel displays date serial numbers such as 47633. Also, I3 refers only to the first row rather than the corresponding Ref Exp value for each filtered row.Try:=FILTER(C3:N500,(H3:H500="YES")+(J3:J500="YES"),"")Then format the Ref Exp and SK Exp columns as Date.You can also check the source dates with =ISNUMBER(I3). If it returns TRUE, the value is a valid Excel date.

peterbartholomew1 · Answer

= LET(
    include?, MAP(refCert="YES", skCert="YES", AND),
    nonBlank, IF(ISBLANK(table), "", table),
    FILTER(nonBlank, include?)
  )As suggested by&nbsp;m_tarler​ , LET allows a formula to developed in readable steps without relying upon 'old-school' tricks such as concatenation.&nbsp; The first line builds the criterion for selection (MAP is needed to ensure the AND function is applied row by row).&nbsp; The next replaces blanks by empty strings across the entire table.&nbsp; The FILTER should then be quite readable, especially so because defined names have been used in place of direct cell references throughout.&nbsp;

Forum Discussion

Advanced Filter

5 Replies