Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with custom date formats
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with custom date formats
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 "Simon Woods" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with custom date formats
Thanks Michael
"Michael Malinsky" wrote in message ... 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 "Simon Woods" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
problem custom sorting a date in an excel file | Excel Discussion (Misc queries) | |||
Date custom formats | Excel Discussion (Misc queries) | |||
importing csv files, problem with date formats | Excel Worksheet Functions | |||
I need a macro to convert my date formats into a custom layout. | Excel Programming | |||
I need a macro to convert my date formats into a custom layout. | Excel Programming |