How would I write a VBA code which converts columns of a table from text to dates whenever the worksheet is changed?

Question

I'm trying to automate an analysis of data for a client. Their system exports the data in text format, including the dates. In order for the dates to be usable in my analysis, they need to be converted from text to dates. Manually, the only way I know is to use the text to columns feature, setting it to delimited with no delimiters and then formatting as dates (since just formatting as dates changes the cell format but doesn't actually covert the inputs). Additionally, I would like this code to run every time the worksheet is modified. (Specifically, each quarter, the non-proficient-in-excel user will copy and paste the new data below the old data, and the table column will again need to be converted). My data is already formatted as a table, so I can reference "Table2[Date Submitted]" to reach the entire data column, even as the data expands.

I'm pretty inexperienced in VBA, and only have basic knowledge. I've been trying to recreate the text to columns sub without luck.

Sub ConvertDates()
'
' ConvertDates Macro
' Converts text dates to usable excel dates MM/DD/YYYY
'
' Keyboard Shortcut: Ctrl+Shift+D
'
    Worksheets(3).Range("Table2[Date Submitted]").TextToColumns Destination:=Range("B9"), DataType:=xlDelimited,
        TextQualifier:=xlNone, ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, _
        3)
End Sub

The sub is incomplete. But I additionally need help building on it.

Answer 1

If I'm understanding your problem correctly you're getting "numbers" that are actually text. While they look like numbers; they aren't.

Dates in Excel are just numbers, have you tried creating a variable say tempHolder and dim as long? Then store one of the cells in that variable and write it to a new cell. in B1 type:

=a1*b1 

This should convert the value to a number. But I'd need an example to toy with.

Write a loop (assuming that works) and you got a way to convert

Answer 2

To change the dates (I assume text formatted like "MM/DD/YYYY"), then try something like:

Sub ChangeDateFormat()

Dim rng As Range
With Sheet1 'Change to your referenced sheet's CodeName
    Set rng = .Range("Table2[Date Submitted]")
    rng.TextToColumns Destination:=rng, DataType:=xlFixedWidth, FieldInfo:=Array(0, xlMDYFormat)
    rng.NumberFormat = "MM/DD/YYY"
End With

End Sub