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 code letter in another cell which will either be "Y" or "N" if the cell is blank or has a date in  it. The cell is formatted as a standard date field.

I have tried ISBLANK, looked at the LEN of the cell but am having problems solving this issue. Any help gratefully received.

Regards

ErnieF

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.

Resources