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!!! :)


    Dani

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

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> 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!