Forum Discussion

WLLIAN's avatar
WLLIAN
Brass Contributor
Oct 25, 2024
Solved

FILTER - HOW NOT SHOW 00/01/1900

THERE IS A WAY TO NOT SHOW 00/01/1900?

ON DATABASE IS BLANK, BUT WHEN I FILTER SHOW THAT DATE.

I WISH SHOW BLANK.

HERE IS THE FORMULA:
=SE(B1="";"";CLASSIFICAR(FILTRO(B2:E40000;ÉNÚM(LOCALIZAR($B$1;B2:B40000))=VERDADEIRO;"");3;1))

 

EXCEL 365

WINDOWS

SYSTEM 64

 

 

excel
Formulas and Functions
office 365
  • Harun24HR's avatar
    Harun24HR
    Oct 25, 2024

    WLLIAN Then you have to use custom number format. Use below custom cell format.

    mm/dd/yyyy;;

    mm/dd/yyyy will apply desired date format to cell having date values greater than 0 means 01/01/1900 and ;; is place holder. After place holder empty means, will hide 0 date values. See your attached file.

     

5 Replies

  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor
    Oct 25, 2024

    WLLIAN Add another criteria to FILTER() function for date column which are not null. Suppose you have dates in E column then try-

    =IF(B1="";"";SORT(FILTER(B2:E40000;(ISNUMBER(FIND($B$1;B2:B40000))=TRUE)*(E2:E40000>0);"");3;1))
    • WLLIAN's avatar
      WLLIAN
      Brass Contributor
      Oct 25, 2024

      Harun24HRThank you, but that way don't show the last result.

      I want all results, but i dont want to show this date 00/01/1900. I think make the sheet ugly kkk.

       

      I wish to stay like this - Empty cell

       

      • Harun24HR's avatar
        Harun24HR
        Bronze Contributor
        Oct 25, 2024

        WLLIAN Then you have to use custom number format. Use below custom cell format.

        mm/dd/yyyy;;

        mm/dd/yyyy will apply desired date format to cell having date values greater than 0 means 01/01/1900 and ;; is place holder. After place holder empty means, will hide 0 date values. See your attached file.

         

Resources