Converting American Date in String format to UK Date format

  • Hi All,

    I'm having problems with converting dates!

    I have imported into Excel from a text file a date which is in an American format - this date however has been imported as a string format so Escel does not recognise it as a date. Then I need to convert that American Date into a british date.

    I have about 400,000 dates to convert!!! So manual is definitely not an option!

    Can anyone help please?

    Eternally grateful if possible!!! :)


  • 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;


    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:

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

    Good luck!

  • 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.



    Excel MVP 2011-2014

    For more Excel memes: visit ==> English articles ==> Excel memes

    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!