trouble formatting dates

i have the date as text in the format as follows:

24 September 2008

in cell B11.

i want to convert this to the format 2008-9-24 in another cell (any cell will do for now!)

i can do this, using =VALUE B11 in B12 and formatting the cell as date, but the month value is 09 and not 9, as i would like. if i use any of the text functions to remove the zero, they treat the formatted cell as '39715' and not as it displays. If there is a way to do this, i do not want it to affect the month when it is double digits (ie when the month is 11, i do not want to remove the leading zero and end up with just 1) please help!!
Regards, jamie

Hi,
Just use the custom format yyyy-m-d

HTH

Thank you, perfect!

sorry to be a pain! how can i now convert this to a text format? the cell i want it in is a variable in a webdata update. it does not recognise the weblink if the cell sshows it in any other format than text. i need the cell to show 2008-9-24, but as text!

Hi,

One way would be a User Defined function (UDF)

In a VBE module enter the following Function

Please Login or Register  to view this content.

now in any cell type =TextDate(A5)
assuming your date is in A5

HTH

With a formula....

=TEXT(B11,"yyyy-m-d")

thankyou both!