how to put date slashes in to a column of number

[bluebean]

hi all,

I have a spreadsheet which has two colums full of numbers, they are dates but just written as numbers i.e. 20050923 for today's date. How can I change them so that they show up as 2005/09/23? I've tried to customize them but it won't work.
thanks!

[moondark]

try the following:

In the format/Cells menu you can enter user defined formats.

Excel won't accept slashes in user defined format so you have to mark as special character by adding a backslash in front of. Just like this:

####\/##\/##

# stands for a variable digit


if doesn't work, try to declare / as text by adding double quotes (####"/"##"/"##)

good luck ,
Simon

[bluebean]

Simon, you're a star, that worked perfectly - thanks a million

[Dave Peterson]

If you really want those values to be dates, you can do this for each column.

select the column
data|text to columns
fixed width (remove any lines that excel guessed)
choose ymd as that field format
finish up the wizard
format that column the way you want

and do the second column.

Numbers that look like dates are usually more confusing than they're worth.

bluebean wrote:
>
> hi all,
>
> I have a spreadsheet which has two colums full of numbers, they are
> dates but just written as numbers i.e. 20050923 for today's date. How
> can I change them so that they show up as 2005/09/23? I've tried to
> customize them but it won't work.
> thanks!
>
> --
> bluebean
> ------------------------------------------------------------------------
> bluebean's Profile: http://www.excelforum.com/member.php...o&userid=25951
> View this thread: http://www.excelforum.com/showthread...hreadid=470165

--

Dave Peterson