SOLVED

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

Copper Contributor

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. 

 

 

8 Replies
best response confirmed by Doug_Robbins_Word_MVP (MVP)
Solution

@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.

@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)

No worries now....I found the solution. It is, custom format with: mm/dd/yyyy;"1/0/1900";;@
But thank you anyway!
I'm glad you came to a solution.
Thanks for the feedback.
Thanks Mark! I couldn't remember this fix. It has been years since I needed it.
How did you figure this out??? This is great

@MarkSanDiego  how did you sort this? im in a similar situation 2 years later.. lol

1 best response

Accepted Solutions
best response confirmed by Doug_Robbins_Word_MVP (MVP)
Solution

@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.

View solution in original post