EXCEL: Converting Serial date strings into dd/mm/yyyy 1

[davidrsutton]

Hi...

I have a list of dates in Excel 97 that for some unknown reason have been formatted to Serial date strings, where each date is represented by a 5 digit number. I need to find a way though to convert all these 5 digit serial dates into dd/mm/yyyy because I really don't want to have to type out over 700 dates again!!!

I have found a function in Excel, DATEVALUE that converts dates (dd/mm/yy) into serial dates but not vice versa. There must be one if you can convert them one way surely you can convert them back? Surely!

Any help would be most appreciated!

Thanks in advance,

Dave

 

[GlennUK]

Just format the cells as date!

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[davidrsutton]

Nice try Glenn... that doesn't work though!!!

 

[AnotherHiggins]

Please provide a couple of examples of the 5 digit numbers and what dates they represent.

[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[xlbo]

First multiply all your "string serial numbers" by 1
To do this, enter a 1 into a blank cell
copy it
select all your string serial numbers
Edit>Paste Special - choose "Values" & "Multiply"

Now format as dd/mm/yyyy

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[davidrsutton]

xlbo!!!

You're a legend... it works!!!!!!!!

Cheers everybody though for their help.

Dave.