Date Format VBA

jaeremata

New Member
Joined
Jan 20, 2021
Messages
22
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
1612531354337.png



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
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,487
Office Version
  1. 2019
Platform
  1. Windows
Hi,

Try

VBA Code:
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
 
Solution

jaeremata

New Member
Joined
Jan 20, 2021
Messages
22
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Hi,

Try

VBA Code:
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

Well-known Member
Joined
Jul 3, 2012
Messages
6,487
Office Version
  1. 2019
Platform
  1. Windows
welcome glad we were able to help

Many thanks for feedback

Dave
 

Watch MrExcel Video

Forum statistics

Threads
1,127,832
Messages
5,627,152
Members
416,224
Latest member
RichardHell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top