+ Reply to Thread
Results 1 to 6 of 6

Macro/vba to change date format from 01.01.2011 to 01/01/2011

  1. #1
    Registered User
    Join Date
    10-01-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    13

    Macro/vba to change date format from 01.01.2011 to 01/01/2011

    Hi,

    I have an excel web query that is pulling off a table into Excel. Column A is basically a list of names and column B is the relevant date. The dates are listed on the website as 01.01.2011 format and are subsequently imported into Excel this way. As a result, I cannot sort column B by date value as Excel is not recognising the format. I’ve tried changing the properties of the date, using find & replace to change it to 01/01/2011 format then changing properties all to no avail.

    Ideally I’d just like some sort of Macro/vba code to change the 01.01.2011 into 01/01/2011 format so that I can sort the column.

    Can anyone help me with this please?
    Last edited by Darrenh1238; 11-20-2012 at 06:32 AM.

  2. #2
    Forum Contributor
    Join Date
    01-17-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    402

    Re: Macro/vba to change date format from 01.01.2011 to 01/01/2011

    Please Login or Register  to view this content.
    This will find . in the date and replace it with /

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Macro/vba to change date format from 01.01.2011 to 01/01/2011

    01/01/2011 is not a great example as it doesn't indicate whether it is dd/mm/yyyy or mm/dd/yyyy.

    Anyway, try selecting the column of dates and using Text to Columns. Choose DMY or MDY as the format and let it replace the values. You can record the steps you take to get the core code for a macro, although it will need tweaking.


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Registered User
    Join Date
    10-01-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Macro/vba to change date format from 01.01.2011 to 01/01/2011

    Thank you both!

    Both ways are just as good - and I completely overlooked using Text to columns.

    Thanks again.

  5. #5
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Macro/vba to change date format from 01.01.2011 to 01/01/2011

    Or just use Find/Replace

    Select the column/range in question, then ...

    Find what:=
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Replace with:=
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Replace all
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Macro/vba to change date format from 01.01.2011 to 01/01/2011

    You're welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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