Forum Discussion
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.
- Thanks Mark! I couldn't remember this fix. It has been years since I needed it.
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 attachMaximum size: 71 MB • Maximum attachments allowed: 5Knowledge of the Excel version would be an advantage.
Thank you for your understanding and patience
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!MarkSanDiego how did you sort this? im in a similar situation 2 years later.. lol
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.
