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]

OK, I think I see what is going on. It looks like they are all coming over as dates, but not maybe not in the format that you want.
It looks like it confused some of the tabs and April 3 is coming over as March 4 (3/4/2023 is being interpretted as March 4 instead of April 3).
I have seen this when exporting from a system that uses a European date format and importing to a system that uses an American Date format.
And if it is using an American format of m/d/yyyy, and try to import a date like 13/4/2023, it will automatically make it text, since there is no month 13.

So I think you may either need to change how the data is being exported out of SQL, or change how it is coming into Excel, so there is consistency there (i.e. both using American formats, or both using European formats).

 

[Anne Troy]

Yep. It's a one-time deal. Not gonna happen. Because you can't POSSIBLY ask the customer to re-run the data. /s

Joe, thanks so much! Yoodaman.

 

[MARK858]

If the dates are appearing in DMY format with the original data change the array in the Text to Columns code to

=Array(1, 4)

Then format the column how you wish