Problem with custom date formats

S

Simon Woods

Hi

I'm programmatically populating a column with dates of the format
"yyyymmdd". I'm setting the NumberFormat for the Column to "yyyymmdd". All
values which are being used are being displayed (eg 20000224) as

#########################

How get I get Excel to display the date "as is" whilst maintaining the date
data-type for the column.

Thanks

Simon
 
M

Michael Malinsky

Excel does not recognize 20000224 as a date but as a number. The number
20000224 does not translate into an Excel date in any way (the highest date
allowed is 12/31/9999 which translates to 2,958,465). In your example, the
date of February 24, 2000 is equivalent to the date value of 36580 in Excel.
If you use 36580 and format it as yyyymmdd you will achieve the desired
result.

--
Michael J. Malinsky
Pittsburgh, PA

"I am a bear of very little brain, and long
words bother me." -- AA Milne, Winnie the Pooh
 
S

Simon Woods

Thanks Michael

Michael Malinsky said:
Excel does not recognize 20000224 as a date but as a number. The number
20000224 does not translate into an Excel date in any way (the highest date
allowed is 12/31/9999 which translates to 2,958,465). In your example, the
date of February 24, 2000 is equivalent to the date value of 36580 in Excel.
If you use 36580 and format it as yyyymmdd you will achieve the desired
result.

--
Michael J. Malinsky
Pittsburgh, PA

"I am a bear of very little brain, and long
words bother me." -- AA Milne, Winnie the Pooh
Click to expand...
 
Top