Forum Discussion

MarkSanDiego's avatar
MarkSanDiego
Copper Contributor
Mar 08, 2022
Solved

Hiding "1/0/1900" when representing empty date formatted cells

I have two sheets in a workbook.  Call them sheet "1" and "2".  A particular cell in sheet 1 references (pulls from) a particular cell in sheet 1.  Both cells in both sheets are "date" formatted.  When the cell in sheet 2 is empty, "1/0/1900" is of course displayed in sheet 1.  But I need to hide or suppress the "1/0/1900" until such time that an actual date is put into the cell in sheet 2.  How do I do this? I tried formatting the sheet 1 cell as a "custom" and used the following: "1/0/1900";"1/9/1900";;@...and that successfully hides/suppresses the 1/0/1900, but the issue is when an actual date is put into the sheet 2 cell, the date format is gone in sheet 1.  

 

I had a similar issue with cells formatted as "general".  When cells in sheet 2 are empty, the referenced cells in sheet 1 of course display "0".  But the solution is easy in this case as one just needs to format the sheet 1 cells as "custom" and use: 0;0;;@.   I'm desperate for an equivalent solution for date formatted cells!!  Thank you for any suggested solutions. 

 

 

  • MarkSanDiego Perhaps a formula like this will work better:

    =IF(ISBLANK(Sheet2!A1),"",Sheet2!A1)

    It will first look is there is something in A1 on Sheet2. If it's empty return an empty string, else return what's in Sheet2!A1.

  • MMCCOY's avatar
    MMCCOY
    Copper Contributor
    Thanks Mark! I couldn't remember this fix. It has been years since I needed it.
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor

    MarkSanDiego 

    If I may recommend you...

    An example file would be very helpful in suggesting a solution...for you.

    Drag and drop here or browse files to attach
    Maximum size: 71 MB • Maximum attachments allowed: 5

    Knowledge of the Excel version would be an advantage.

     

    Thank you for your understanding and patience

     

    NikolinoDE

    I know I don't know anything (Socrates)

    • MarkSanDiego's avatar
      MarkSanDiego
      Copper Contributor
      No worries now....I found the solution. It is, custom format with: mm/dd/yyyy;"1/0/1900";;@
      But thank you anyway!
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor

    MarkSanDiego Perhaps a formula like this will work better:

    =IF(ISBLANK(Sheet2!A1),"",Sheet2!A1)

    It will first look is there is something in A1 on Sheet2. If it's empty return an empty string, else return what's in Sheet2!A1.

Resources