I have a date fiels yyyymmdd which can have a value of 0 or a valid date in
recent past.
Excel displays the 0 date as 01/01/1900 and the non zero dates as expected.

Cant figure this out , any help would be appreciated.

Excel uses a sequencial number from either 1/1/1900, or 1/1/1904 (depending
on your options) as the date value
for Example 6/3/2005 is 38506 using the 1/1/1900
if the cell is formatted as a date it is responding with the date it thinks
you mean.
If you want the cell to display a zero
=if(Date=0,"0",date)

"DP NY10601" wrote:

I have a date fiels yyyymmdd which can have a value of 0 or a valid date in
recent past.
Excel displays the 0 date as 01/01/1900 and the non zero dates as expected.

Cant figure this out , any help would be appreciated.

yyyymmdd is not a valid excel date format, excel needs date deimiters or else
it will treat it as a number.. Excel year zero started on Jan 0 1900 so if you
put 0 in a cell it will return 01/00/1900, 1 will be 01/01/1900

If you get a date in yyyymmdd format, select it, do datatext to columns and
click next twice, select Date under column data format and from dropdown
select YMD
and click finish and it will be converted to a real excel date

Regards,

Peo Sjoblom


"DP NY10601" wrote:

I have a date fiels yyyymmdd which can have a value of 0 or a valid date in
recent past.
Excel displays the 0 date as 01/01/1900 and the non zero dates as expected.

Cant figure this out , any help would be appreciated.

Just to add to Peo's instructions.

Before you do the Data|Text to columns and before you format that column as a
date,

Edit|Replace
what: 0
with: (leave blank)
replace all

It's less typing than:
Edit|Replace
what: 01/00/1900
with: (leave blank)
replace all


Peo Sjoblom wrote:

yyyymmdd is not a valid excel date format, excel needs date deimiters or else
it will treat it as a number.. Excel year zero started on Jan 0 1900 so if you
put 0 in a cell it will return 01/00/1900, 1 will be 01/01/1900

If you get a date in yyyymmdd format, select it, do datatext to columns and
click next twice, select Date under column data format and from dropdown
select YMD
and click finish and it will be converted to a real excel date

Regards,

Peo Sjoblom

"DP NY10601" wrote:

I have a date fiels yyyymmdd which can have a value of 0 or a valid date in
recent past.
Excel displays the 0 date as 01/01/1900 and the non zero dates as expected.

Cant figure this out , any help would be appreciated.

--

Dave Peterson