Jul 18 2019 07:00 AM
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 it just converts the column to all midnight of a random date. The screenshot shows the number values in the first column, each different TIME format option that i can choose, and the complete text that excel converts the number to. None of the options come up with JUST a HH:MM:SS. They all have a date.
Jul 18 2019 07:43 AM
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.
Jul 18 2019 11:21 AM
@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.
Jul 23 2019 03:12 PM
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
Jul 23 2019 03:37 PM
Hi
This issue can be fixed in one of 2 ways without the need of formulas and functions
Method 1:
Power Query
Method 2:
Text To Column
Hope that helps
Nabil Mourad
Jul 23 2019 04:04 PM
IMHO, both won't convert 123456 into 12:34:56
Jul 23 2019 04:08 PM - edited Jul 23 2019 04:11 PM
Hi Sergei, How are you... It's too late in the evening now :)
it's exactly 43670.0902777778
Well, you know, I am talking about the rightmost column in the screenshot...
When we do not have sample Excel file then we are just guessing what the question is
Take care my friend
Nabil Mourad