CODEWITHSUNDEEP
vbaexceldatetimetext
Anna Bedzinska
Anna Bedzinska

Reputation: 11

Converting hh:mm:ss to text using VBA for Excel

I have a column formatted in hh:mm:ss within the cells there is also a yyyy-mm-dd added. They look something like this:

1970-01-01 00:01:25

I want it to look like this formatted in text:

00:01:25

If I go about using text to columns manually to remove the yyyy-mm-dd and only importing the 00:01:25 part as text, it works fine.

But if I create a macro doing the same I end up with:

:01:25 AM

My macro looks like this:

Columns("D:D").Select
Selection.TextToColumns Destination:=Range("D1"), DataType:=xlFixedWidth, _
    FieldInfo:=Array(Array(0, 9), Array(12, 2)), TrailingMinusNumbers:=True

Thank you all, I have now got it to work by useing the following code(I got help from another board:

Sub m()
With Range("D2:D" & Range("D" & Rows.Count).End(xlUp).Row)
    .NumberFormat = "@"
    .Cells = Evaluate("=INDEX(TEXT(" & .Address & ",""HH:MM:SS""),0)")
End With
End Sub

Upvotes: 1

Views: 3038

Answers (1)

kolcinx
kolcinx

Reputation: 2233

It can be done using excel formula Text(text;format) see picture and formula bar.

enter image description here

Or you can just apply custom text formatting to the original column.

enter image description here

Or do you need to do this in VBA?

Edit. Solution using VBA

Range("D:D").NumberFormat = "hh:mm:ss"

Upvotes: 1

Related Questions