Excel date format

[aprisa]

Apart from my head exploding trying to work with office 2007 I have a specific problem.

we have a stock list that has the dates arranged as 01.01.07 etc when I know they should be 01/01/07.

Excel will not sort these without changing them but there are about 500!

Can I carry out some form of formula to change the . for / ?

Nick

[UncleDave]

IIRC.. You can highlight the lot, right click, cell properties.. Then if you set the cell format to Date it should pick the dates up and give an option to change the seperator if needed..?
I'm no excel expert but worth a try..!

Dave.

Edited by UncleDave on Friday 16th February 15:06

[mmm-five]

If you are sure the cells contain the text in the format dd.mm.yy, then simply selecting the column/row they're in and then do a 'replace all' replacing the "." with "/"

[plasticpig]

=DATE(RIGHT(TRIM(A1),2),MID(TRIM(A1),4,2),LEFT(TRIM(A1),2)) should do the trick. Obviously you need to change A1 to the cell reference for the date.

[aprisa]

I can't seem to get a replace formula to work in Excel 2007?