Date Format VBA

[jaeremata]

Hi Everyone, I need you help regarding with this code. I just got it also here. I have a macro that will import another excel, all the text covert into data. It works, but when I import another excel for the next month, some date still remain in text. If you see on the screen shot, what I want to happen is to convert text to date. for column J and K

Here's the code:

Sub MyDateConvert()

Dim myFirstCol As String
Dim myAllCols As Variant
Dim myLastRow As Long
Dim i As Long
Dim myCol As String
Dim myCell As Range

Application.ScreenUpdating = False

' Specify columns to apply to
myAllCols = Array("J", "K")

' Find last row with data
myLastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

' Loop through all columns
For i = LBound(myAllCols) To UBound(myAllCols)
myCol = myAllCols(i)
' Format column as text
Columns(myCol).NumberFormat = "YYYY-MM-DD"
' Loop through cells in column, converting the ones that have values to text in "yyyy-mm-dd" format
For Each myCell In Range(Cells(2, myCol), Cells(myLastRow, myCol))
If Len(myCell) > 1 Then myCell = Format(myCell, "YYYY-MM-DD")
Next myCell
Next i

Application.ScreenUpdating = True

End Sub

 

[mart37]

Maybe: myCell.Value = CDate(myCell.Value)

 

[dmt32]

Hi,

Try

For Each myCell In Range(Cells(2, myCol), Cells(myLastRow, myCol))
        With myCell
            If IsDate(.Value) Then
                .Value = DateValue(.Value)
                .NumberFormat = "YYYY-MM-DD"
            End If
        End With
    Next myCell

Dave

 

[jaeremata]

Hi,

Try

For Each myCell In Range(Cells(2, myCol), Cells(myLastRow, myCol))
        With myCell
            If IsDate(.Value) Then
                .Value = DateValue(.Value)
                .NumberFormat = "YYYY-MM-DD"
            End If
        End With
    Next myCell

Dave

Yey. Thank you for the idea. I was missing the value. thank you.

 

[dmt32]

welcome glad we were able to help

Many thanks for feedback

Dave