Convert text dates
 

I would like to convert a column of dates into the format dd/mm/yy.
The dates are in text format. e.g. "January 01 2006".

Many thanks.

Convert text dates
 

Data/ Text to Columns/ Delimited, at the end select Date, & choose the
format MDY.

That will convert your text to a date, and then you can format as dd/mm/yy.
--
David Biddulph

"AmyGG" wrote in message
ups.com...
I would like to convert a column of dates into the format dd/mm/yy.
The dates are in text format. e.g. "January 01 2006".

Many thanks.

Convert text dates
 

David Biddulph wrote:

Data/ Text to Columns/ Delimited, at the end select Date, & choose the
format MDY.

That will convert your text to a date, and then you can format as dd/mm/yy.
--
David Biddulph

Sorry, I tried this but the text stayed the same even after I
reformmated it to dd/mm/yy.

Convert text dates
 

Hi Amy,

This formula seems to work OK....

Assuming your date is in Cell F5,

=VALUE(MONTH(FIND(" ",F5))&"/"&(MID(F5,(FIND("
",F5)+1),2))&"/"&(RIGHT(F5,4)))

Then format the resulting number into whatever date formula you need.

To explain what the formula is doing (so that you can modify it
yourself if you get any dates that are different from this one)


MONTH(FIND(" ",F5) looks for the character number of the first space
(in this case 8) - therefore those 8 characters must be the month name,
which MONTH then changes into the correct month number (1). Note there
is a space between the speech marks in this bit as well as after FIND
further along.

MID is looking for the day number (01) and does that by adding 1 to the
number of characters that FIND returns again, and since the day is 2
digits it gets 01.

RIGHT gets the year from the 4 characters from the end of your date,
working rightwards (2006)

=VALUE converts all that lot into a number (38718), which is the number
of days between your date and 1st Jan 1900 - and that's why you need to
format it back to being a date.

Got all that?

Hope it helps

Regards

Phil
)


AmyGG wrote:

David Biddulph wrote:

Data/ Text to Columns/ Delimited, at the end select Date, & choose the
format MDY.

That will convert your text to a date, and then you can format as dd/mm/yy.
--
David Biddulph

Sorry, I tried this but the text stayed the same even after I
reformmated it to dd/mm/yy.

Convert text dates
 

It worked for me (and has just done so again as I tried it on a different
machine). Are you sure you went though the steps listed in my post?

Did you definitely choose Date and MDY under Column Data Format at the last
stage of the Data/ Text to Columns operation?
--
David Biddulph

"AmyGG" wrote in message
oups.com...
David Biddulph wrote:

Data/ Text to Columns/ Delimited, at the end select Date, & choose the
format MDY.

That will convert your text to a date, and then you can format as
dd/mm/yy.
--
David Biddulph

Sorry, I tried this but the text stayed the same even after I
reformmated it to dd/mm/yy.