How To avoid 1/1/1900 instead show blanks in dates in Excel.

April 20, 2016 at 06:34:26
Specs: Windows 7
Formatting the whole column as mm/dd/yyyy;; will make it to show blanks instead of the 1/1/1900, to exclude in criteria >0

Alvaro Ortiz


See More: How To avoid 1/1/1900 instead show blanks in dates in Excel.

Report •


#1
April 20, 2016 at 07:16:41
I'm not sure I understand what you are trying to do.

The formatting alone will not force the cell to display 1/1/1900. The cell must contain a 1 or a formula that evaluates to 1 in order for 1/1/1900 to be displayed.

Please provide some more details as to what is actually in the cell so we can get a better understanding of your issue.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
April 20, 2016 at 07:34:14
Very neat trick, but not sure I see the value in using it.

When you see 1/1/1900 in a Date cell that usually means an invalid value has been entered.
If your doing a calculation, and the result will not be a Date, then perhaps the cell should not be formatted as a Date cell?

Here is another way of doing the same thing, but this will display a Zero in the formula bar not 01/00/1900:

Custom Format: [=0]"";General

MIKE

http://www.skeptic.com/


Report •

#3
April 20, 2016 at 07:39:04
DerbyDad

The cell must contain a 1 or a formula that evaluates to 1

Not necessarily.
If a cell is formatted using any of the usual DATE formats,
then a Zero will trigger the 1/0/1900.

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
April 20, 2016 at 07:43:35
Just as a clarification,

If the cell is formatted using any of the usual Date Formats,
entering a Zero results in 01/0/1900
entering a One results in 01/1/1900
entering a Two results in 01/2/1900
etc. etc.

MIKE

http://www.skeptic.com/


Report •

#5
April 20, 2016 at 08:39:31
Mike,

re:

Me: The cell must contain a 1 or a formula that evaluates to 1
You: Not necessarily.

If you'll review what I wrote, you will see that I addressed the OP's specific situation:

"...will make it to show blanks instead of the 1/1/1900..."

Yes, a 0 will result in 1/0/1900 and a 2 will result in 1/2/1900, but he didn't asked about those dates, he asked about 1/1/1900.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
April 20, 2016 at 08:50:02
DerbyDad,

OK, on re-reading your reply, I see where you were specifically replying to his 1/1/1900 concerns and not just the generalized 1900 issue.

MIKE

http://www.skeptic.com/


Report •


Ask Question