Date to text

[oldham94l]

can i change 23/10/02 from a date format to text format so either 23 october 2002, or 23/10/02

 

[IML]

If this date is in cell A1, what happens when you enter the formula =a1+0 in a blank cell?

 

[oldham94l]

nope, still a date format

 

[IML]

I thought the answer would either be a value! error or 23/10/2002.

Use text to columns under the data menu. Highlight, hit next, next again, and finally choose DMY under date.

If you want a formula you could use
=DATE(RIGHT(A1,4),MID(A1,FIND("/",A1)+1,FIND("L",SUBSTITUTE(A1,"/","L",2))-FIND("/",A1)-1),LEFT(A1,FIND("/",A1)-1))

A much simplier formula could be developed if your months and days are always two digits in the current format. This is more a generic catch all.
This message was edited by IML on 2002-10-23 16:43