+ Reply to Thread
Results 1 to 14 of 14

VBA solution to convert mixed UK/US date formats

  1. #1
    Registered User
    Join Date
    05-07-2014
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2016, 365
    Posts
    7

    Question VBA solution to convert mixed UK/US date formats

    Hi All

    I have a macro to import data from a .csv file that contains dates in DD/MM/YYYY format - which is the format I need.

    At some point in the process, some of the dates are converted to MM/DD/YYYY.

    I am using Excel 2016 and I think I have a solution using "Text to Columns" but when I record it as a macro, the step that involves formatting the column data as MDY does not appear to get recorded.

    How would I write this into the code? (Assuming this is the right way to do it)

    Please Login or Register  to view this content.
    Thanks in advance
    Last edited by Pepe Le Mokko; 01-24-2020 at 07:23 AM.

  2. #2
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow

    Hi,

    bad import as you forgot to define the column format !

    Just activate the Macro Recorder, open the csv text file like a workbook then well answer to the Import Assistant
    in particular for the column format (it's the same process if you still use the text to columns convert feature)

  3. #3
    Registered User
    Join Date
    05-07-2014
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2016, 365
    Posts
    7

    Re: VBA solution to convert mixed UK/US date formats

    Thanks for the reply

    I have tried changing the way the data is pasted in from the source file but it is still changing dates such as 08/01/2020 to 01/08/2020.

    Unfortunately the problem I have is that the Macro Recorder is not recording the column format. I need to know how I can add that manually.

    Thanks

  4. #4
    Forum Expert
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    2,203

    Re: VBA solution to convert mixed UK/US date formats

    Upupload a sample csv with false data (to protect data confidentiality) but reliable/suitable/proper in terms of data layout.
    Your computer's system settings are affecting the result of the data import and we have to somehow bypass it, but for this we need a data sample.

  5. #5
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,527

    Re: VBA solution to convert mixed UK/US date formats

    @Garry

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however you need to include code tags around your code.

    Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

    Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    I did it for you this time. Please read forum rules. Thanks

  6. #6
    Registered User
    Join Date
    05-07-2014
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2016, 365
    Posts
    7

    Re: VBA solution to convert mixed UK/US date formats

    Thanks Pepe, noted.

  7. #7
    Registered User
    Join Date
    05-07-2014
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2016, 365
    Posts
    7

    Re: VBA solution to convert mixed UK/US date formats

    Thanks, below is more of the macro I use to import the data:

    Please Login or Register  to view this content.
    Attached Files Attached Files

  8. #8
    Forum Expert nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    Office 365 Windows 10
    Posts
    2,293

    Re: VBA solution to convert mixed UK/US date formats

    Have you tried converting the .csv file to .txt before importing

    I just changed the file extension to .txt and imported and got "08/01/2020"
    Last edited by nigelog; 01-24-2020 at 08:19 AM.

  9. #9
    Registered User
    Join Date
    05-07-2014
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2016, 365
    Posts
    7

    Re: VBA solution to convert mixed UK/US date formats

    Thanks, I've tried that but, although it works manually, it doesn't seem to work in a macro.

  10. #10
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow

    As it works a treat on my side whatever operating manually or just using the procedure from the Macro Recorder
    but I just notice your code does not well define the appropriate column format, so just activate the Macro Recorder and retry …

  11. #11
    Registered User
    Join Date
    05-07-2014
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2016, 365
    Posts
    7

    Re: VBA solution to convert mixed UK/US date formats

    Thanks everyone

    I've found a solution by converting the column containing the dates to numbers before I import.

    I still don't know why my macro recorder ignored my formatting though!

    Once again - thanks to all.
    Last edited by GarryDH; 01-24-2020 at 09:39 AM.

  12. #12
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow


    The Macro Recorder can not ignore anything so you just choosed a wrong format …

  13. #13
    Registered User
    Join Date
    05-07-2014
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2016, 365
    Posts
    7

    Re: VBA solution to convert mixed UK/US date formats

    I don't know what the issue was - it worked fine when I recorded it but differently when run as a macro. There was nothing in VB to show what formatting I had set.

    Thanks for your help anyway.

  14. #14
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow


    Wrong as the formatting belongs to the FieldInfo parameter of the TextToColumns method
    as you can yourself check within the VBA inner help !

    And before to validate the Assistant, just check the column, it's just visual so easy to compare with the original text file …

+ 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. Convert date formats
    By asad.ermo in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-10-2018, 09:25 AM
  2. Replies: 19
    Last Post: 01-31-2016, 12:29 PM
  3. [SOLVED] mixed date formats in s/s, how to format
    By cal_chica in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-16-2014, 06:22 PM
  4. Excel file has mixed date formats
    By pushnoyj in forum Excel General
    Replies: 1
    Last Post: 02-14-2014, 07:45 PM
  5. Mixed US and UK date formats in Excel Spreadsheet
    By Buntrock in forum Excel General
    Replies: 15
    Last Post: 11-07-2012, 10:58 AM
  6. Replies: 4
    Last Post: 08-25-2012, 07:49 AM
  7. Convert date formats mmm-yy to dd-mm-yy
    By acjarus in forum Excel General
    Replies: 10
    Last Post: 05-29-2012, 10:53 AM

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