Forum Discussion
Time Conversion Issue
So I am trying to converter the IND Time/CON Time from number to time. The problem seems to be that its trying (no matter what format in the time section i pick) to put a date in then a time and i...
Hi
Format column B as Time
Then use the formula =--(LEFT(A3,2)&":"&MID(A3,3,2)&":"&(RIGHT(A3,2)))
The formula parses the value in column A into its three numeric components and adds a ":" in between each set.
The double unary minus "--" at the beginning of the formula then coerces the resultant text to a numeric and it displays with the correct time format.
Roger Govier This almost solved it!! The only issue I ran into was single digit hours didn't make the cut or formatted kind of crazy.
Couple of more variants
=--REPLACE(REPLACE(A1,INT(LEN(A1)/2),0,":"),LEN(A1),0,":")
and
=TIME(LEFT(A1,LEN(A1)-4),LEFT(RIGHT(A1,4),2),RIGHT(A1,2))
applying HH:MM:SS format to the result
