Excel vba: Convert String to Datetime add it to new Column

Question

So I have cells containing strings of date, such as:

14/04/2019 10:13:18 AM

how can I convert it to DateTime using vba?

I've tried using .NumberFormat but some of the cells got converted and some didn't:

My code is

Sub ConvertToDateTime()

    With Range("Data[Modified On]")
        .NumberFormat = "dd/mm/yyyy hh:mm:ss AM/PM"
        .Value = .Value
    End With

End Sub

And how do I insert converted value to a new column? I've created a new column with:

    Dim Table As ListObject
    Dim newColNum As Integer

    Set Table = ActiveSheet.ListObjects("Data")
    Table.ListColumns.Add.Name = "New Header"

Can I do it without looping?

How about you first copy the range to a new column and then you convert that column's format to a DateTime format? — AAA
– AAA, Commented Jun 3, 2019 at 13:04

Abdallah El-Yaddak · Accepted Answer · 2019-06-03 14:36:35Z

1

Some of the cell do got converted but some don't

That's because the date format on your PC is different than the data (it is mm/dd/yyyy while the data is in "dd/mm/yyyy")

This can't be fixed without looping. (as far as I know) To fix that, you might need to do something like this:

Sub ConvertToDateTime()
    Dim Cell As Range, h As Long, c As Long
    Range("Data[New Header]").NumberFormat = "dd/mm/yyyy hh:mm:ss AM/PM"
    c = Range("Data[New Header]").Column
    For Each Cell In Range("Data[Modified On]")
        If Right(Cell.Value, 2) = "PM" Then h = 12 Else h = 0
        Cells(Cell.Row, c).Value = DateSerial(Mid(Cell.Value, 7, 4), Mid(Cell.Value, 4, 2), Left(Cell.Value, 2)) + _
            TimeSerial(Mid(Cell.Value, 12, 2) + h, Mid(Cell.Value, 15, 2), Mid(Cell.Value, 18, 2))
    Next
End Sub

answered Jun 3, 2019 at 14:36

Abdallah El-Yaddak

4663 gold badges11 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

espifi059 espifi059 Over a year ago

Thanks!, i ended up using looping you showed me and add 6 hours to my original string, Cells(Cell.Row, c).Value = DateAdd("h", 6, Cell.Value), i was wondering if i can do this without looping like in pyhton.

Abdallah El-Yaddak Over a year ago

Interesting! Are you sure it works even with the dates where the day number is higher than the month number? Slicing the string and feeding the numbers to DateSerial & TimeSerial makes sure that Excel understands the date correctly. Are you sure DateAdd does that too? (It did not on when I tested) No, I am not aware of anything that can do this without looping in Excel.

espifi059 espifi059 Over a year ago

i think yes, i check the converted date manually (not all), so far it converted and add the time correctly. :D

Abdallah El-Yaddak Over a year ago

Don't forget to choose an answer as "acceptable" when you find one.

Collectives™ on Stack Overflow

Excel vba: Convert String to Datetime add it to new Column

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related