+ Reply to Thread
Results 1 to 8 of 8

American Dates to UK Dates

  1. #1
    Registered User
    Join Date
    07-08-2019
    Location
    Cork
    MS-Off Ver
    365
    Posts
    3

    American Dates to UK Dates

    Hi all,

    The problem is when I open a .csv with a bunch of American dates in it Excel formats all the days that are before the 12th of the month as UK dates and it formats the rest as text.

    I made this formula today based on what people have said in the past on this forum.

    Please Login or Register  to view this content.
    Has anyone got a shorter formula for solving this problem? What I have seems a bit excessive for what it's doing.
    Yes I'm sharing this so if other people want to do the same thing it is here.

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,637

    Re: American Dates to UK Dates

    Select the colum, Data > Text to columns, Next, Next, tick Date, select MDY, Finish.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,095

    Re: American Dates to UK Dates

    Have you tried using Data>Text to columns... to convert the dates?

    To do that select the column with the dates, goto Data>Text to columns... and on the third step select MDY from the date format dropdown.
    If posting code please use code tags, see here.

  4. #4
    Registered User
    Join Date
    07-08-2019
    Location
    Cork
    MS-Off Ver
    365
    Posts
    3

    Re: American Dates to UK Dates

    I have tried that, it wasn't an ideal option though because I need the list to be automatic and using Test to Columns requires manual input.
    Thanks for the reply Norie

  5. #5
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,095

    Re: American Dates to UK Dates

    How are you importing the file?

    PS Isn't using a formula kind of manual too?

  6. #6
    Registered User
    Join Date
    07-08-2019
    Location
    Cork
    MS-Off Ver
    365
    Posts
    3

    Re: American Dates to UK Dates

    I am opening the .csv file and copy pasting the list into my spreadsheet, the list is about 3000 rows.

    I agree it is a manual operation since I still have to do the copy paste but once it is done the values in the rest of the spreadsheet populate automatically.
    I think I'm saving myself at least three clicks with this.

  7. #7
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,095

    Re: American Dates to UK Dates

    If you use Data>Text to columns... there would be no need to copy/paste special.

    As for clicks, I think Data>Text to columns... would only be about 4-5.

    PS You could also import the data into Excel via Data>From Text... where you should get the opportunity to deal with dates in the Import Wizard.

  8. #8
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,035

    Re: American Dates to UK Dates

    I think I'm saving myself at least three clicks with this.
    Record a macro while doing this and then from now on it's one click.
    HTH
    Regards, Jeff

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Looking for a formula to convert American dates to British dates.
    By JackMWhit in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-23-2020, 05:47 AM
  2. Macro changing dates to American from UK
    By gallen6945 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-20-2014, 09:31 AM
  3. All dates in American format?
    By Simon Lloyd in forum Suggestions for Improvement
    Replies: 2
    Last Post: 01-03-2013, 08:35 AM
  4. Macro Dates - American or English
    By simmo86 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-26-2010, 09:03 AM
  5. dates on a userform are american!!!
    By excely in forum Excel General
    Replies: 1
    Last Post: 04-14-2008, 07:31 AM
  6. [SOLVED] Excel American dates
    By Duncan in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 04-12-2006, 09:30 AM
  7. [SOLVED] convert american dates from a query
    By Mika in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-02-2005, 06:20 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1