Date Format when incoming date can be 0

D

DP NY10601

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

bj

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)
 
P

Peo Sjoblom

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 data>text 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
 
D

Dave Peterson

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
 
Top