PDA

View Full Version : [SOLVED] How do I convert text or string to time format using excel vba?



spittingfire
07-29-2016, 05:58 PM
I have a report and in columns L to Y I have the time of hh:mm:ss written as text and as a result I'm unable to do pivot calculations.
Is there an excel vba script that I can use to convert columns L:Y to the time value of hh:mm:ss?
any help would be appreciated.
Thanks

mikerickson
07-29-2016, 09:04 PM
Select each column and use TextToColumns it will convert the text entries into Excel serial times.

SamT
07-29-2016, 09:11 PM
Sub T()

Application.ScreenUpdating = False

Lr = Cells(Rows.Count, "Y").End(xlUp).Row
x = Range("L1:Y" & Lr).Value
Range("L1:Y" & Lr).NumberFormat = "hh:mm:ss"
Range("L1:Y" & Lr) = x

Application.ScreenUpdating = True
End Sub


Something interesting I discovered: With a Text formatted as Time, place the cursor before the time string in the formula bar and pressing Backspace will convert that cell format to a number format with the time's serial value. I could not find an equivalent VBA action

spittingfire
07-30-2016, 09:15 AM
Thanks SamT and mikerickson - most appreciated. Both options works but for simplicity I've opted to go with the VBA solution suggested by SamT.