Add time reference

Hi Experts,

I need Expert help. How add-in automatically data at Column A with “:” and “.” E.g 06451800 become 06:45:18.00. Hope Experts could help.

Who is Participating?
andrewssd3Connect With a Mentor Commented:
For VBA, try this:

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
    Next c
    rngLook.NumberFormat = "hh:mm:ss.00"

End Sub

Open in new window

Do you want the result to be a real Excel time value, or just a string with the : and . added?
If so, this formula in Column B will do it:


Open in new window

It does not work with your first value, which you have converted to a number.
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

wchhConnect With a Mentor Commented:
Try the formula below:
=LEFT(A2,2) &":"&MID(A2,3,2)&":"&MID(A2,5,2)&"."&MID(A2,7,2)
Or for A2, length = 7,
=IF(LEN(A2)=8,LEFT(A2,2) &":"&MID(A2,3,2)&":"&MID(A2,5,2)&"."&MID(A2,7,2),"0" &LEFT(A2,1) &":"&MID(A2,2,2)&":"&MID(A2,4,2)&"."&MID(A2,6,2))
CartilloAuthor Commented:

Is that possible to create a conditional formatting at column A?
barry houdiniConnect With a Mentor Commented:
Thuis formula in B2 should do what you need


format B2 as hh:mm:ss.00 and copy down - see attached

What do you need conditional formatting to do?

regards, barry
barry houdiniConnect With a Mentor Commented:
....if you mean you want to input data as shown and for it to automatically convert to a time value - that isn't possible without VBA......unless you "cheat" and use a custom format like this


With that if you enter 1234123 then that will display as


....but it won't be a valid time value that you can use in calculations

regards, barry
CartilloAuthor Commented:

Hi Barry,

Sorry to complicate the whole think. Can you help to put this via VBA? When I import the actual data this column will be automatically added with “:” and ‘:”. Hope you will consider this.
barry houdiniCommented:
VBA isn't my area of expertise - I'll see if anybody else can help you out......

CartilloAuthor Commented:
Hi andrewssd3,

Cool! thanks a lot for the help.
Question has a verified solution.

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.

All Courses

From novice to tech pro — start learning today.