Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Atlanta, Georgia
    Posts
    136
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Within Excel 2010, I need to convert dates that are formatted mmddyyyy to ddmmyyyy. Have tried Custom number formats and can't seem to get anything to work. Ideas anyone?

    Thanks,

    Ken

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    I assume these are text string, not real dates.
    Do you want to just change the text strings , or convert them to real dates?

    If you want to convert the string use a formula

    =Mid(CellRef,3,2)&Left(CellRef,2)&Right(CellRef,4)

    If You want to convert the string to a real date then use

    =Date(Right(CellRef,4),Left(CellRef,2),Mid(CellRef ,3,2)) and use a format to show as a date

    Where CellRef is the appropriate Cell Reference.
    Andrew

  3. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Here's a vba solution, that changes the order within the cells concerned:
    Code:
    Sub Demo()
    Dim oCel As Range, TmpVal As Long
    For Each oCel In Selection
      If IsNumeric(oCel.Value) And oCel.HasFormula = False Then
        If Len(oCel.Value) = 8 Then _
          TmpVal = Mid(oCel.Value, 3, 2) & Left(oCel.Value, 2) & Right(oCel.Value, 4)
        If Len(oCel.Value) = 7 Then _
          TmpVal = Mid(oCel.Value, 2, 2) & "0" & Left(oCel.Value, 1) & Right(oCel.Value, 4)
        oCel.Value = TmpVal
      End If
    Next
    End Sub
    I've assumed the 'dates' are simply numbers, in which case, there'll be some that are only 7 digits long as the leading 0 won't be held for months less than 10.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  4. #4
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    This sample uses a DATEVALUE formula to convert a text date to a number and then choose a date format that is suitable.

    The DATEVALUE formulas are in Column B.
    Attached Files Attached Files

Posting Permissions

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