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.
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.