How do i turn a column of these (62000) into this (06:20:00)

[Colin.O]

Hi Guys

I have a problem. I've been trying to make an add-in that compares two columns and highlights any times that are more than 15 minutes apart.

The only problem, is that one of the columns is in hh:mm:ss format and the other is just numerical.

I've attached the file if anyone wants a closer look.

I'm eally stuck and would appreciate any help

Thanks

Colin

[JBeaucaire]

E1: =TIMEVALUE(LEFT(B1,8)&"."&RIGHT(B1,2))
F1: =TIMEVALUE(IF(LEN(C1)=5, TEXT(LEFT(C1,1),"00:")&TEXT(MID(C1,2,2),"00:")&RIGHT(C1,2), TEXT(LEFT(C1,2),"00:")&TEXT(MID(C1,3,2),"00:")&RIGHT(C1,2)))
G1: =ABS(E1-F1)

Copy those 3 cells downward.

Highlight those 3 columns and apply a custom number format of: [h]:mm:ss.00

Note in some rows E>F, in other F>E. The ABS() function in column G will always subtract the higher number from the lower.

[Pete_UK]

You could put this formula in E1:

=IF(LEN(C1)=5,"0","")&LEFT(C1,LEN(C1)-4)&":"&MID(C1,LEN(C1)-3,2)&":"&RIGHT(C1,2)

and copy down. This is a text value that looks like a time - if you want it as an Excel time then you can do this:

=--(IF(LEN(C1)=5,"0","")&LEFT(C1,LEN(C1)-4)&":"&MID(C1,LEN(C1)-3,2)&":"&RIGHT(C1,2))

and format as time. Note, though, that column B is not in time format, as there are 4 sets of numbers separated by colons.

Hope this helps.

Pete

[daddylonglegs]

You can convert C1 with this formula

=TEXT(C1,"00\:00\:00")+0

format as time

...or you could get the difference directly with this formula

SUBSTITUTE(B1,":",".",3)-TEXT(C1,"00\:00\:00")

As Jerry says, in some rows that will be negative, do you want to show negative values?