change text formatting

[alethea2000]

Hi I am going to reword this another way

I have the following data

08/18/2005
06/14/2004
07/25/2003
07/30/2003

and excel recognises it as straight text but i need it to be in uk data format, does anyone know how to do it as custom dd\mm\yyyy won't work as excel doesn't recognise it as a date. if you copy above into a spreadsheet you'll see what i mean,

i so want to go and pick up my baby but can't until i figure this out, what a nightmare and thank you

 

[Joe4]

If it is truly in TEXT format, this function will convert it to TEXT which is your UK date format.

Assuming the data is in cell A1:
=MID(A1,4,3)&LEFT(A1,3)&RIGHT(A1,4)

 

[fairwinds]

Hi,

Two options:

--a formula: =DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,4,2))
format as you wish


--Data - Text to columns, in last step you can choose date format.

 

[Andrew Poulsom]

Click the column header and choose Data|Text to Columns from the menu. At Step 3 choose Date MDY and click Finish.