Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Jul 2008
    Posts
    25
    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. #2
    2 Star Lounger
    Join Date
    Mar 2010
    Location
    Tampa, FL, USA
    Posts
    114
    Thanks
    11
    Thanked 9 Times in 8 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

  3. #3
    Lounger
    Join Date
    Jul 2008
    Posts
    25
    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

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,496
    Thanks
    212
    Thanked 852 Times in 784 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


  5. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,979
    Thanks
    0
    Thanked 208 Times in 189 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]

  6. #6
    5 Star Lounger
    Join Date
    Mar 2014
    Posts
    699
    Thanks
    0
    Thanked 65 Times in 64 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.

  7. #7
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,979
    Thanks
    0
    Thanked 208 Times in 189 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
  •