spectrum48k

a column of 200 cells
each cell has date entered in the format dd.mm.yy eg. 31.06.05
problems stems from the cells being formatted as "text"

Is there any experienced Excel uses who can tell me how to convert these cells so they're formatted correctly eg. set to dd/mm/yyyy

I've tried selecting the column and manually setting format cells to "date" to no avail. No effect at all.

Any help would be appreciated

HHxx

Got me thinking, so I had a go. I'm normally crap at Excel.

Column A = formatted as text - current text formatted date.
Column B = formatted as date - =DATE("20"&RIGHT(A1,2),MID(A1,4,2),LEFT(A1,2))

That did it for me. The "20" part is because on my Excel it kept getting the year 1905.

Hopefully that works for you

H

spectrum48k

You're a star H! Thanks so much!

SPEN555

Or Highlight the cells and cleck I think Edit then find and replace. Type in '.' (full stop) and in replace put '/' forward slash. Then do the same for '05' to '2005'. Leave the cells as text though as it will end up dividing it.

spectrum48k

Good thinking Spen - dead simple, but I bet it works a treat also.

Dracoro

Careful though. Apart from dates being in the 19XX range. There's an Excel setting that you can set what year it goes from 20xx to 19xx, IIRC it's 32 (i.e. 31- = 2031, 32+ = 1932). Also you've asked for TWO digits, this is fine if the date is 12/10/94 but what about if the numbers are single values 1/9/94?

spectrum48k

Dracoro, I was fortunate that all numbers were of the format dd.mm.yy

...so there wasn't anything like 1/9/94

HHxx

Glad it worked for you

Next time I better ask for a requirements documents to cover all bases