Need VBA code to change date format of Column A

Enrique A. Santos KM4ZQC
Enrique A. Santos KM4ZQC used Ask the Experts™
on
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.
AUTO-REGION-10-UTILIZATION-REPORT-2.xlsm
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Wayne Taylor (webtubbs)

Commented:
They all look fine to me. What formula is trying to use them?
Managing Director/Excel VBA Developer
Distinguished Expert 2018
Commented:
Hi,

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("B:C").Insert
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"
Range("B:C").Delete
Application.ScreenUpdating = True
End Sub

Open in new window

Enrique A. Santos KM4ZQCCustomer Service Representative

Author

Commented:
That's it!!!
Wayne Taylor (webtubbs)

Commented:
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

Commented:
Wow!

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