Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dates formatted as text | Excel Discussion (Misc queries) | |||
imported text data converting to dates | Excel Discussion (Misc queries) | |||
convert numbers to text | Excel Discussion (Misc queries) | |||
Convert text to numbers | Excel Worksheet Functions | |||
convert value stored as text to logical refrence value! | Excel Worksheet Functions |