1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel 2010 UK Date to US Date

Discussion in 'Business Applications' started by Sunnyd204, Jul 23, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. Sunnyd204

    Sunnyd204 Thread Starter

    Joined:
    Jul 23, 2012
    Messages:
    3
    I am frustrated with the following problem.
    I need to convert the date to US formatting.


    I receive a file in .csv format and the dates come as text and a normal date format all within the same column.

    Example:

    12/4/2012 23:16
    12/4/2012 23:21
    13/04/12 12:04 AM
    13/04/12 12:52 AM
    13/04/12 3:20 AM

    The top 2, Excel reads as date. The bottom 3 excel reads as text...I need the bottom 3 to read as date.

    Please HelP!!!
     
  2. 20_2_Many

    20_2_Many

    Joined:
    Jun 29, 2012
    Messages:
    518
    Are you Importing the file into excel (Data > From Text), choosing Delimited > Next > Selecting Comma as the Delimeter > Next > Changing the Column Data Format for your Date Field to Date and Selecting MDY, DMY, or YMD - whichever you Prefer > Changing any other format you may need in other columns > Finish > Chooing the Cell to beging the Data import from > OK.
    ?
     
  3. Sunnyd204

    Sunnyd204 Thread Starter

    Joined:
    Jul 23, 2012
    Messages:
    3
    The file that I open is a pre-formatted csv file.
    In other words, there is no comma delimiting necessary because the data is already split out in to the respective columns.

    I have tried the method you suggested, and it did not work.
    My guess is I need to be able to convert the UK date formatting to US. As soon as the day is 13 and up it changes it to a text cell rather than a date.
     
  4. 20_2_Many

    20_2_Many

    Joined:
    Jun 29, 2012
    Messages:
    518
    Try this.
    I did not mess with the Hours except to get them out of the way.Hope your .csv does not actually come with multiple hour formats!
    Run the PrepColumns. It adds 2 columns to hold the Hours and moves hours over. Then Calls the Change to UK macro.
    This starts at bottom and works up to Row 2, swapping the Day and Month in each cell.
    Test Reset just puts it back to the Date and Hour info from your original post.
    Good Luck
     

    Attached Files:

    • USUK.xls
      File size:
      48.5 KB
      Views:
      110
  5. etaf

    etaf Wayne Moderator

    Joined:
    Oct 2, 2003
    Messages:
    55,859
    i dont think its the 13 - its the AM thats the issue

    its because you appear to have a time format in the csv file of 24 hour clock and 12 hour clock mixed

    1st two are in 24 hour and last 3 have AM in the field - but i think 20_2_many has replied with a macro to resolve
     
  6. Garf13LD

    Garf13LD

    Joined:
    Apr 17, 2012
    Messages:
    455
    ermm... its to US not UK
    The first 2 dates reads 4 Dec 2012, which are incorrect.
    The last 3 reads 4 mth(13) 2012, which are invalid.
    Swapping the dd/mm to mm/dd will solve all the problem.
    One way is to do a find replace in MS word.
    1. open the csv file in word.
    2. load up the replace dialog (Ctrl + H)
    3. Click more and check use wildcards
    4. in the search field use one or the following
    ([0-9]{2})/([0-9]{2})/(*M) for date format ##/##/##
    ([0-9]{1,2})/([0-9]{1,2})/(*M) for date format with 1 or 2 digits day mth
    5. in the replace field use the following
    \2/\1/\3
    6. save the file and reopen in excel


    if you need it in excel formula or vba macro, let us know.
     
  7. 20_2_Many

    20_2_Many

    Joined:
    Jun 29, 2012
    Messages:
    518
    I probably did swap the splits inadvertently.
    ukdate = x(1) & "/" & x(0) & "/" & x(2)
    or
    ukdate = x(0) & "/" & x(1) & "/" & x(2)
    will provide the mm/dd/yyyy or dd/mm/yyyy format whichever is desired.
     
  8. Garf13LD

    Garf13LD

    Joined:
    Apr 17, 2012
    Messages:
    455
    Excel treat date as numbers, hence after PrepColumns "split", ChangeToUK is not able to swap "real dates"

    This formula I have written years back will fix it.
    of cos, I will rewrite it into vba when I have time.
    =VALUE(MID(TEXT(A1,"DD/MM/YY"),FIND("/",TEXT(A1,"DD/MM/YY"),1)+1,3)&LEFT(TEXT(A1,"DD/MM/YY"),FIND("/",TEXT(A1,"DD/MM/YY"),1))&RIGHT(TEXT(A1,"DD/MM/YY"),2))
     
  9. Sunnyd204

    Sunnyd204 Thread Starter

    Joined:
    Jul 23, 2012
    Messages:
    3
    I tried a few of the options suggested and it worked like a charm!

    Thanks Everyone:D
     
  10. etaf

    etaf Wayne Moderator

    Joined:
    Oct 2, 2003
    Messages:
    55,859
    your welcome (y) thanks for letting us know
    You can mark your own threads solved using the [​IMG] button at the top of the page of the thread in the upper left corner. :)
     
  11. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1062282