Converting American Date in String format to UK Date format

Hi Dani

The best way would be to use the Text Import Wizard (Data>Import external data>Import data) to import the dates and at the last step choose the required date format.

Or, if say cell A1 has the text date is US format (mm-dd-yy) use;

=DATEVALUE(MID(A1,4,2)&"/"&LEFT(A1,2)&"/"&RIGHT(A1,2))

Copy down as needed. Then select, copy and Edit>Paste special - Values over the top. then format to the required date format.

This can usually be done fairly easily using a combination of RIGHT, LEFT, MID and possibly LEN and other functions. Can you post a few examples of exactly what your text strings look like. e.g. 13 June 03, or 07/03/03, or 7/3/2003, etc. We'll need that to know how to structre the parsing formula.

HI,

select the date ( Or the cloumn which contains the date), From the menu bar select>> "Text to Column"- Select first option> "Delimited", Click "Next" and once again" Next". Here you will get an option at - right top corner which states " column data format". here click date and select the date format you want to see. click finish,

this will work, have a nice day

Rajeev Gupta

Re: Converting American Date in String format to UK Date format

You could use the following formula to toggle between US and UK dates:
1. Enter the dates into a column in the spreadsheet (e.g. Column B)
2. Right click Column B -> Format Cells -> Number -> Category = Text -> OK
3. Paste the following formula in Column C (e.g. Row 2):
=CONCATENATE(MID(B2,SEARCH("/", B2,1)+1,2), "/" ,LEFT(B2,SEARCH("/", B2,1)-1), "/", RIGHT(B2,4))

The above formula will work when the dates are separated by the character "/". If the dates you have are separated by "-" or ".", replace the "/" with the character you have.This formula should toggle between the dd/mm/yyyy and mm/dd/yyyy styles.

Hope this helps.

Re: Converting American Date in String format to UK Date format

I ran into this problem when downloading reports that came in US format, because they downloaded as Text and I couldn't just change the formatting.

Split cells works awesomely and the formula above ^^^ works for mm/dd/yyyy but the data I was downloading was text and it could be m/dd or mm/dd or mm/d or dd/m (!) AHHH!!

Anyways, this formula works in that occasion:
=CONCATENATE((IF(MID(B11,4,1)="/",MID(B11,3,1),IF(MID(B11,2,1)="/",MID(B11,3,2),IF(MID(B11,6,1)="/",MID(B11,4,2),MID(B11,4,1))))),"/",IFERROR((IF(SEARCH("/",LEFT(B11,2)),LEFT(B11,1),LEFT(B11,2))),LEFT(B11,2)),"/",RIGHT(B11,4))

Where B11 is the date you are formatting from US to UK format.

Good luck!
Rb

Re: Converting American Date in String format to UK Date format

Thank you, but posting in topics that are so old is probably not so relevant anymore.