Forum Discussion

ErnieF's avatar
ErnieF
Copper Contributor
Dec 16, 2018

Date Formatting

I have a series of cells with a vlookup formula which either returns a date (if the lookup table has a date) or a blank. This works fine. I am trying to pick up the value of that cell and return a co...
Excel
Haytham Amairah's avatar
Haytham Amairah
Silver Contributor
Dec 16, 2018

Hi,

 

ISBLANK works only with the completely blank cells.

If the blank is returned by a formula, then you have to check whether the cell is blank or not using this formula:

=IF(A1="","Y","N")

 

Hope that helps

  • ErnieF's avatar
    ErnieF
    Copper Contributor
    Dec 17, 2018

    Thanks Haytham, but I am afraid it does not work. Although the cell appears to be blank, If I ask for the LEN(cell ref) I get "01/01/1900". I have tried using that in the formula, but that does not work either.

    The lookup formula works perfectly to either display a date or a blank cell, but I need to identify if it is displaying a date then another cell gets to show a code letter.

    Regards

    Ernie

     

    • Haytham Amairah's avatar
      Haytham Amairah
      Silver Contributor
      Dec 17, 2018

      Hi Ernie,

       

      I can't figure out what exactly you're trying to do!

      I understood that you have a date column contains dates and some blank cells, all returned by VLOOKUP.

      And you want in another column to check whether the cell is blank or not.

      So I have suggested this formula:

      =IF(A1="","Y","N")

       

      I don't know why you use the LEN function?

      If can provide a sample worksheet attached here, that's would be better to figure out what is the problem.

    • SergeiBaklan's avatar
      SergeiBaklan
      MVP
      Dec 17, 2018

      As a comment, it looks like misprint is here

      LEN(cell ref) I get "01/01/1900"

      it shall be "00/01/1900" (if dd/mm/yyyy format), other words LEN(cell ref) returns 0.

Resources