Reformat custom date format

[bdb1974]

Hi all,

I'm hoping someone can help provide a simple solution to a problem that I created by having a custom date format.

I have 2 columns with dates in a custom format like: yyyy,mm,dd
I don't think the system recognizes this as a valid date format eventhough I formated
the cells the custom format mentioned.

I want to change the format to: mm,dd,yyyy

I would like to do something like the following:

Sub FixTimeFormats()

Dim lngYellow As Long
Dim lngWhite As Long
Dim I As Integer
Dim FindIt As Variant
Dim DF As Worksheet
Dim WS2 As Worksheet

    With ThisWorkbook
    ' ComboBox1.Visible = False
     lngYellow = RGB(252, 248, 61)
    lngWhite = RGB(255, 255, 255)
  
    'Set DF = Sheets("DATEFORMAT")
   Set WS2 = Sheets("Sheet1")
      
WS2.Activate


 'Lastrow = WS2.Cells(Rows.Count, "A").End(xlUp).Row

     Dim M As String
  Dim D As String
  Dim Y As String
 
 Lastrow = WS2.Cells.Find(What:="*", After:=[K1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Row
 
    For Each Cell In Range("$K$8:$K$" & Lastrow, "$Q$8:$Q$" & Lastrow)
    MsgBox Cell.Value & " " & Cell.Row
   A = Cell
    If Cell Like "[0-9][0-9][0-9][0-9][,][0-9][0-9],[0-9][0-9]" Then
   Y = Mid(Cell.Value, 1, 4)
   M = Mid(Cell.Value, 6, 2)
   D = Mid(Cell.Value, 9, 2)
   Cell.Value = M & "/" & D & "/" & Y
   With Cell
   DateFormat = "MM/DD/YYYY"
   End With
   ElseIf Cell Like "[0-9][0-9][/][0-9][0-9][/][0-9]][0-9]" Then
     Y = Mid(Cell.Value, 1, 4)
   M = Mid(Cell.Value, 6, 2)
   D = Mid(Cell.Value, 9, 2)
   Cell.Value = M & "/" & D & "/" & Y
   With Cell
   DateFormat = "MM/DD/YYYY"
   End With
   End If
   
   Next

        End With

End Sub

Any suggestions as to why this is not working?

I've attached a sample.

Thanks,

BDB

[JBeaucaire]

This is why you always enter dates as normal date values, then use cell formatting to make them display in any funky way you want. The values in your cells are not dates.

Use this formula in an adjacent cell to get the true date values and put them back in the cells, then your existing cell formatting will make them display any way you want:

=DATE(LEFT($K9,4)+0,MID($K9, 6,2)+0, RIGHT($K9,2)+0)