Change Date from UK to US format

[clintsmyth]

I'm downloading a bank statement from HSBC and need to be able to convert the date from a UK 31/10/11 format to US 10/31/11. If someone has a formula or code that can do this I would be grateful!

Thanks in advance!

Clint

 

[venkat1926]

suppose A1 has the entry 31/10/11(England entry)
in B1 copy this formula
=MID(A1,4,2)&"/"&LEFT(A1,2)&"/"&RIGHT(A1,4)
you will get amreican date
you can even copy down if there is data in col. A
one condition. the english dats should be in the form dd/mm/yyyy
that is if date is 1 it should be 01

 

[dave3009]

Or if it is a true date then =TEXT(A1, "mm/dd/yyyy")

 

[smerrick]

To avoid using formulas, try the following (excel 2003):

<TABLE style="WIDTH: 261pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=348 border=0 x:str><COLGROUP><COL style="WIDTH: 261pt; mso-width-source: userset; mso-width-alt: 11136" width=348><TBODY><TR style="HEIGHT: 18.75pt" height=25><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 261pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 18.75pt; BACKGROUND-COLOR: transparent" width=348 height=25>Highlight your row of dates
Then press:
</TD></TR><TR style="HEIGHT: 18.75pt" height=25><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 18.75pt; BACKGROUND-COLOR: transparent" height=25>Data</TD></TR><TR style="HEIGHT: 18.75pt" height=25><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 18.75pt; BACKGROUND-COLOR: transparent" height=25>Text to columns</TD></TR><TR style="HEIGHT: 18.75pt" height=25><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 18.75pt; BACKGROUND-COLOR: transparent" height=25>Delimited</TD></TR><TR style="HEIGHT: 18.75pt" height=25><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 18.75pt; BACKGROUND-COLOR: transparent" height=25>Next</TD></TR><TR style="HEIGHT: 18.75pt" height=25><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 18.75pt; BACKGROUND-COLOR: transparent" height=25>Next</TD></TR><TR style="HEIGHT: 18.75pt" height=25><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 18.75pt; BACKGROUND-COLOR: transparent" height=25>Select date and in the drop down select MDY</TD></TR><TR style="HEIGHT: 18.75pt" height=25><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 18.75pt; BACKGROUND-COLOR: transparent" height=25>Then finish</TD></TR></TBODY></TABLE>

 

[venkat1926]

"Select date and in the drop down select MDY"

should it be DMY or MDY