Multiply all DATE cells (some are text formatted) by 1 using VBA.

[Anne Troy]

SORRY--I CHANGED THE METHOD I'M FOCUSING ON, BUT I DON'T CARE HOW IT'S DONE. Only need to do this once on 6 files with about 20 worksheets each.

I have 6 files with thousands of records in each. Someone must've changed their SQL query because the first half of the month shows as actual date values and the rest of the month is text dates.

The code below works, but I have to select the range.
I want it to work on column D from 2nd row to last row.
I want it to work on ALL worksheets in the workbook.

Sub ConvertStringToDate()

    Dim i As Integer

    For i = 2 To 5000
   
        Range("D" & i) = CDate(Range("D" & i))
    Next i
   
End Sub

Halp!! AND THANKS IN ADVANCE!

 

[Joe4]

Anne, I think we can do this without a dreaded loop for the range, using Text to Columns.
What format is the date in?
Can you show us a few examples?

 

[Joe4]

I think your code should look something like this:

Sub ConvertStringToDate()

    Dim ws As Worksheet
    
    Application.ScreenUpdating = False
    
'   Loop trhough all sheets
    For Each ws In ActiveWorkbook.Worksheets
'       Do "Text to Columns" on Column D converting to date
        ws.Columns("D:D").TextToColumns Destination:=ws.Range("D1"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
            Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
            :=Array(1, 3)
    Next ws
    
    Application.ScreenUpdating = True
    
End Sub

 

[Anne Troy]

Anne, I think we can do this without a dreaded loop for the range, using Text to Columns.
What format is the date in?
Can you show us a few examples?

Sure. Unfortunately, some are 16/3/2023 and some are 3/16/2023.

An actual file is here: www.annetroy.com/mrexcel/sample3.xlsx

Some dates are actual date values that convert to 40,000-something when formatted as numbers.
Others are just text.

See the early days in the month -vs- the later days in the month.

 

[Anne Troy]

I think your code should look something like this:

Sub ConvertStringToDate()

    Dim ws As Worksheet
   
    Application.ScreenUpdating = False
   
'   Loop trhough all sheets
    For Each ws In ActiveWorkbook.Worksheets
'       Do "Text to Columns" on Column D converting to date
        ws.Columns("D:D").TextToColumns Destination:=ws.Range("D1"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
            Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
            :=Array(1, 3)
    Next ws
   
    Application.ScreenUpdating = True
   
End Sub

Are you ALWAYS the one person that helps me? Seems like it, Joe. Thanks so much. I'll check this out.

 

[Anne Troy]

Sure. Unfortunately, some are 16/3/2023 and some are 3/16/2023.

An actual file is here: www.annetroy.com/mrexcel/sample3.xlsx

Some dates are actual date values that convert to 40,000-something when formatted as numbers.
Others are just text.

See the early days in the month -vs- the later days in the month.

I ran it. Get no errors and no changes. Thanks!

 

[Joe4]

For some reason, I cannot open the link.

Sure. Unfortunately, some are 16/3/2023 and some are 3/16/2023.

Of those, are some always text (like the "16/3/2023") and some always dates (like "3/16/2023")?

 

[Anne Troy]

Formula bar contents on 5 April: 5/4/2023
Format: m/d/yyyy

Formula bar contents on 20 April: 4/20/2023
Format: General

 

[Anne Troy]

http://www.annetroy.com/mrexcel/sample3.xlsx

Not sure why it's not working. Always does. Try Index of /mrexcel

 

[Anne Troy]

PS: I started out by finding and replacing 5/4 with 4/5, then found out the 2nd half of the workbook was "general".