Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Jul 2008
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need macro to convert date formats

    Hi,

    I wanted to change the date format for a specific column from MDY to DMY using Text to Columns in excel for 4 files. This is specific records in that column and not for the entire column. How to define the Range of selected rows/cells in that column to change the format ? Require help...

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    2 Star Lounger
    Join Date
    Mar 2010
    Location
    Tampa, FL, USA
    Posts
    114
    Thanks
    11
    Thanked 7 Times in 7 Posts
    If the data is recognized as a date by EXCEL, then I'd suggest changing the cell format, not the data itself. EXCEL can display in DMY format by setting a Custom format to the string DDMMMYY to see 01Jan14 for example.

    Otherwise, I'd need more detail to understand what the problem you're trying to solve is to offer a suggestion, however, it appears you could look at using the LIKE operator to find the cells in the column you want to modify and write your code around that condition.
    PJ in FL

  4. #3
    New Lounger
    Join Date
    Jul 2008
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts
    As mentioned earlier, I need Specific records in a Column to be changed to DMY through text to colums.... Cell Formatting didn't work.
    Example: 2-Nov-14 has to be changed to 11-Feb-14

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,194
    Thanks
    201
    Thanked 785 Times in 719 Posts
    Mohanss,

    If I read your problem correctly it appears that you have TEXT in the format 2-Nov-14 but when you use formatting you get 11-Feb-14. This would indicate to me that the original was created by a system that had the date format set to month-day-year and is now being read on a system with the date format set to day-month-year or vise-a-versa. Since the data in in TEXT format you will need a more elaborate conversion process to tear apart the text string and reassemble it into the proper format.

    If this assumption is correct please post back and we'll get to work on code to accomplish this task. HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  6. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,899
    Thanks
    0
    Thanked 188 Times in 172 Posts
    The following macro converts date strings and values in the selected range between mm/dd/yyyy and dd/mm/yyyy. The output is in the form of date values that you can format as dates via Format|Cells|Number|Date.
    Code:
    Sub ConvertDateFormat()
    Dim DtRange As Range, oCell As Range, oTxt As String
    If Selection.Cells.Count = 1 Then
      Set DtRange = ActiveCell
    Else
      Set DtRange = Selection
    End If
    With Application
      On Error Resume Next ' In case there are no xlConstants or convertible dates in DtRange
      For Each oCell In DtRange.SpecialCells(xlConstants)
        oTxt = oCell.Text
        If UBound(Split(oTxt, "/")) = 2 Then _
          oCell.Value = CDate(Split(oTxt, "/")(1) & "/" & Split(oTxt, "/")(0) & "/" & Split(oTxt, "/")(2))
      Next oCell
    End With
    End Sub
    Note that no conversion is performed if the date is embedded in a formula or the output would be an invalid date.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  7. #6
    4 Star Lounger
    Join Date
    Mar 2014
    Location
    Forever West USA
    Posts
    500
    Thanks
    0
    Thanked 44 Times in 44 Posts
    I may be missing something but rather than changing the formatting of dates do the two dates shown in the question, 2-Nov-14 and 11-Feb-14, have any relevance? Seems to me the need is to change the actual date rather than how it is shown.

  8. #7
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,899
    Thanks
    0
    Thanked 188 Times in 172 Posts
    Quote Originally Posted by Berton View Post
    Seems to me the need is to change the actual date rather than how it is shown.
    That's what my macro does...
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Tags for this Thread

Posting Permissions

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