Date problem

[Gerry]

I have a column containing
4/21/2004
10/7/2003
5/5/2004
Which I need to convert to UK dates.
because of the variables(4/, 10/) I am unable to use mid
etc in seperate columns.
also =TEXT(f2,"DD/MM/YY")fails to work.

 

[Frank Kabel]

Hi
are these values 'real' date values. If yes a simple format should do

 

[Gerry]

No Frank, they were not just real date values.
I was able to resolve with the help of the great people
here by
"I had the orginal date in G4
Then in G5 I put
=IF(MID(G4,2,1)="/","0"&G4,G4)
this adds a zero to make it mm
then in the column next to this (i.e. G6 for me) put
=IF(MID(H4,5,1)="/",LEFT(H4,3)&"0"&RIGHT(H4,6),H4)
this is based on the new one you just calculated therefore
this is mm/dd/yyyy"
Then by inserting extra rows with mid functions in
converted all.
Thanks To All

 

[Dave Peterson]

Another option would be to use Data|Text to columns.

Use mdy as the format of the field (column????), but when you're done change the
format to dd/mm/yyyy (or what you like.)

But data|text to columns is a quick way to turn "text" dates into real dates.