supress 01-01-1900

[Peter]

Hi.

I have a column coming from an sql-base.

When the data are displayed as values I can supress 0. But when I show
the data in date-format 0 is shown as 01-01-1900. Thus the value are
shown again.

What am I doing wrong ?

Regards

Peter

 

[joeu2004]

When the data are displayed as values I can supress 0. But when I show
the data in date-format 0 is shown as 01-01-1900. Thus the value are
shown again. What am I doing wrong ?

Not telling us what revision of Excel you use and how you are
suppressing zeros in the first place.

I presume that you have a typo and zero formatted as Date actually
displays 01-00-1900 or 00-01-1900 depending on Regional and Language
settings.

In Excel 2003, suppress zero by unchecking the option "Zero Values"
under Tools > Options > View. That works for zero formatted as Date
as well as any other numeric format.

Of course, the real correction might be: do not format that cell as
Date in the first place.

Typically, we see 01-00-1900 because we are doing an operation with
dates (like subtraction) that results in a scalar (simple number), but
Excel decides to format the result as Date because it incorrectly
thinks that is what we want.

We must override Excel's behavior by explicitly setting the format to
Number or some other numeric format other than General.

 

[joeu2004]

I presume that you have a typo and zero formatted as Date actually
displays 01-00-1900 or 00-01-1900 depending on Regional and Language
settings.

On the other hand, if you truly see 01-01-1900, you do not have a zero
value in the first place. Instead, you have the value 1 formatted as
Date.

Presumably you do not want to suppress that value.

Again, the correction is....

 

[Peter]

On Mon, 6 Dec 2010 06:46:25 -0800 (PST), Peter

Something is strange if a zero is showing as 01-01-1900.  Ordinarily,
I would expect it to show as 01-00-1900 or something similar depending
on your exact format.

To suppress a zero in the date field, you can use a custom format
like:

or similar.

Note the 2 semicolons at the end.

Oh, and if for some reason you want to suppress both the zero and a
one (the one would give you the output you posted), you can use this
custom format:

[>1]m/d/yyyy;;- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

Hej.

Thank You both.

Your advice, Ron, was what I was looking for.

The reason why I not mentioned the excel version is, that the
challenge has been there, and still is, since versions.

You are right, that it is strange that 0 is formatet to be January
1st. I still wonder why.

Best regards, and glædelig jul to all of you.

Peter