+ Reply to Thread
Results 1 to 5 of 5

How to change a date format?

  1. #1
    Registered User
    Join Date
    04-08-2009
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    85

    How to change a date format?

    I've got some dates in the format Sep 27, 2016 which I want to convert to the format 27/9/2016
    - it won't format using the Data Format command. Any help welcome!

  2. #2
    Forum Contributor kalbasiatka's Avatar
    Join Date
    02-27-2016
    Location
    Brest, Belarus
    MS-Off Ver
    2021
    Posts
    224

    Re: How to change a date format?

    Please Login or Register  to view this content.
    To do it for me and help me it is 2 different things!
    Sorry for my english, blame Google translator

  3. #3
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: How to change a date format?

    I'm guessing you imported data from text and your dates are seen by Excel a Text-Dates and not Serial-Dates. If you select one of the dates and if the value as seen in the formula bar is a short date format d/m/yyyy, then it's a Serial-Date. Otherwise it's a Text-Date.

    There are a couple of methods to convert Text-dates to Serial-dates. One that should work in this case is to select your column (or range) of dates, Ctrl+H to open the Replace dialog and replace the comma with a comma. That can force Excel to evaluate each text date into a serial-date. Then you should be able to set the dates format.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: How to change a date format?


  5. #5
    Registered User
    Join Date
    04-08-2009
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    85

    Re: How to change a date format?

    That's weird - no idea how that question cloned itself. Thanx for answers anyhow!

+ 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. Show Error when a cell with number format change to date format
    By delroba in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-08-2015, 02:57 AM
  2. [SOLVED] Date formula - Change Format to YYYYMMDD from Exported Format
    By haleakala17 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-21-2014, 03:22 PM
  3. [SOLVED] help on vba code to change the error date format as standard format mm/dd/yyyy
    By johnodys in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-13-2013, 01:10 AM
  4. Excel:How change the textbox format from text to date format?
    By inpetto in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-10-2013, 08:23 AM
  5. Macro to change date value from one date format to another date format
    By RG12 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-23-2013, 04:51 AM
  6. Not able to change date format into proper excel date format
    By excel5111987 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-18-2011, 08:19 AM
  7. Userform date format reverting back to us format on change event
    By jpruffle in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-10-2009, 12:34 PM

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