Your technology certification is waiting. Enroll in Cloud Class ®
Public Sub FixTimes()
Dim c As Excel.Range
Dim rngLook As Excel.Range
Dim strTime As String
' get the used part of column A, excluding the first row
Set rngLook = Application.Intersect(Range("A:A"), ActiveSheet.UsedRange)
Set rngLook = rngLook.Offset(1, 0).Resize(rngLook.Rows.Count - 1, 1)
For Each c In rngLook.Cells
strTime = c.Value2
strTime = String(8 - Len(strTime), "0") & strTime
' only do eight character fields
strTime = Mid(strTime, 1, 2) & ":" & _
Mid(strTime, 3, 2) & ":" & _
Mid(strTime, 5, 2) & "." & _
Mid(strTime, 7, 2)
c.Value2 = strTime
rngLook.NumberFormat = "hh:mm:ss.00"
Open in new window
Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Please enter a first name
Please enter a last name
Must be at least 4 characters long.
Join and Comment
From novice to tech pro — start learning today.
Premium members can enroll in this course at no extra cost.