Need VBA code to change date format of Column A

Enrique A. Santos KM4ZQC
Enrique A. Santos KM4ZQC used Ask the Experts™
Column A has several dates, but it's not readable for formula, please provide VBA code to convert all the date at once. Thank you.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
They all look fine to me. What formula is trying to use them?
Managing Director/Excel VBA Developer
Distinguished Expert 2018

Please try below, change the date format as you want.
Sub ChangeDateFormat()
Dim r As Long
Dim Ws As Worksheet
Application.ScreenUpdating = False
Set Ws = ActiveSheet

r = Ws.UsedRange.Rows.Count

Ws.Range("A4:A" & r).TextToColumns Destination:=Ws.Range("A4"), DataType:=xlDelimited, _
  TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
  Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
  :=Array(Array(0, 4), Array(10, 1), Array(24, 1)), TrailingMinusNumbers:=True
Range("A4:A" & r).NumberFormat = "mm/dd/yy"
Application.ScreenUpdating = True
End Sub

Open in new window

Enrique A. Santos KM4ZQCCustomer Service Representative


That's it!!!
Hmmm. The same can be done with a single line of code...

Range("A4:A" & Activesheet.UsedRange.Rows.Count).NumberFormat = "mm/dd/yy"

Open in new window

The reason being is that they are already real dates. Just format them how you want.
Bill PrewIT / Software Engineering Consultant
Top Expert 2016


Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial