SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-3249847%22%20slang%3D%22en-US%22%3EHiding%20%221%2F0%2F1900%22%20when%20representing%20empty%20date%20formatted%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3249847%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20two%20sheets%20in%20a%20workbook.%26nbsp%3B%20Call%20them%20sheet%20%221%22%20and%20%222%22.%26nbsp%3B%20A%20particular%20cell%20in%20sheet%201%20references%20(pulls%20from)%20a%20particular%20cell%20in%20sheet%201.%26nbsp%3B%20Both%20cells%20in%20both%20sheets%20are%20%22date%22%20formatted.%26nbsp%3B%20When%20the%20cell%20in%20sheet%202%20is%20empty%2C%20%221%2F0%2F1900%22%20is%20of%20course%20displayed%20in%20sheet%201.%26nbsp%3B%20But%20I%20need%20to%20hide%20or%20suppress%20the%20%221%2F0%2F1900%22%20until%20such%20time%20that%20an%20actual%20date%20is%20put%20into%20the%20cell%20in%20sheet%202.%26nbsp%3B%20How%20do%20I%20do%20this%3F%20I%20tried%20formatting%20the%20sheet%201%20cell%20as%20a%20%22custom%22%20and%20used%20the%20following%3A%20%221%2F0%2F1900%22%3B%221%2F9%2F1900%22%3B%3B%40...and%20that%20successfully%20hides%2Fsuppresses%20the%201%2F0%2F1900%2C%20but%20the%20issue%20is%20when%20an%20actual%20date%20is%20put%20into%20the%20sheet%202%20cell%2C%20the%20date%20format%20is%20gone%20in%20sheet%201.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20had%20a%20similar%20issue%20with%20cells%20formatted%20as%20%22general%22.%26nbsp%3B%20When%20cells%20in%20sheet%202%20are%20empty%2C%20the%20referenced%20cells%20in%20sheet%201%20of%20course%20display%20%220%22.%26nbsp%3B%20But%20the%20solution%20is%20easy%20in%20this%20case%20as%20one%20just%20needs%20to%20format%20the%20sheet%201%20cells%20as%20%22custom%22%20and%20use%3A%200%3B0%3B%3B%40.%26nbsp%3B%20%26nbsp%3BI'm%20desperate%20for%20an%20equivalent%20solution%20for%20date%20formatted%20cells!!%26nbsp%3B%20Thank%20you%20for%20any%20suggested%20solutions.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3249847%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3278108%22%20slang%3D%22en-US%22%3ERe%3A%20Hiding%20%221%2F0%2F1900%22%20when%20representing%20empty%20date%20formatted%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3278108%22%20slang%3D%22en-US%22%3EThanks%20Mark!%20I%20couldn't%20remember%20this%20fix.%20It%20has%20been%20years%20since%20I%20needed%20it.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3251307%22%20slang%3D%22en-US%22%3ERe%3A%20Hiding%20%221%2F0%2F1900%22%20when%20representing%20empty%20date%20formatted%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3251307%22%20slang%3D%22en-US%22%3EI'm%20glad%20you%20came%20to%20a%20solution.%3CBR%20%2F%3EThanks%20for%20the%20feedback.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3251238%22%20slang%3D%22en-US%22%3ERe%3A%20Hiding%20%221%2F0%2F1900%22%20when%20representing%20empty%20date%20formatted%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3251238%22%20slang%3D%22en-US%22%3ENo%20worries%20now....I%20found%20the%20solution.%20It%20is%2C%20custom%20format%20with%3A%20mm%2Fdd%2Fyyyy%3B%221%2F0%2F1900%22%3B%3B%40%3CBR%20%2F%3EBut%20thank%20you%20anyway!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3249889%22%20slang%3D%22en-US%22%3ERe%3A%20Hiding%20%221%2F0%2F1900%22%20when%20representing%20empty%20date%20formatted%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3249889%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1315070%22%20target%3D%22_blank%22%3E%40MarkSanDiego%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%3EIf%20I%20may%20recommend%20you...%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%3EAn%20example%20file%20would%20be%20very%20helpful%20in%20suggesting%20a%20solution...for%20you.%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CDIV%20class%3D%22%22%3E%3CDIV%20class%3D%22%22%3EDrag%20and%20drop%20here%20or%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fforums%2Freplypage%2Fboard-id%2FExcelGeneral%2Fmessage-id%2F137317%23%22%20target%3D%22_blank%22%3Ebrowse%3C%2FA%3E%20files%20to%20attach%3C%2FDIV%3E%3CDIV%20class%3D%22%22%3EMaximum%20size%3A%2071%20MB%20%E2%80%A2%20Maximum%20attachments%20allowed%3A%205%3C%2FDIV%3E%3C%2FDIV%3E%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%3EKnowledge%20of%20the%20Excel%20version%20would%20be%20an%20advantage.%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20understanding%20and%20patience%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel-blog%2Fmeet-niko-chatzoudis-excel-forum-contributor%2Fba-p%2F2941385%22%20target%3D%22_blank%22%3ENikolinoDE%3C%2FA%3E%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3249887%22%20slang%3D%22en-US%22%3ERe%3A%20Hiding%20%221%2F0%2F1900%22%20when%20representing%20empty%20date%20formatted%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3249887%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1315070%22%20target%3D%22_blank%22%3E%40MarkSanDiego%3C%2FA%3E%26nbsp%3BPerhaps%20a%20formula%20like%20this%20will%20work%20better%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DIF(ISBLANK(Sheet2!A1)%2C%22%22%2CSheet2!A1)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EIt%20will%20first%20look%20is%20there%20is%20something%20in%20A1%20on%20Sheet2.%20If%20it's%20empty%20return%20an%20empty%20string%2C%20else%20return%20what's%20in%20Sheet2!A1.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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. 

 

 

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