• 580K Members
• 4,500 Online
• 701K Conversations

Highlighted
New Contributor

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 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.

6 Replies
Highlighted

Re: Time Conversion Issue

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.

Highlighted

Re: Time Conversion Issue

@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.

Highlighted

Re: Time Conversion Issue

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

Highlighted

Re: Time Conversion Issue

Hi

This issue can be fixed in one of 2 ways without the need of formulas and functions

Method 1:

Power Query

1. Convert the column showing date and time  (or even the entire list) into Table (CTRL +T)
2. Click on the Data Tab >> From Table >> Most probably there will be an automatic change in Data Type to the correct Date. If Not
3. Select the Date column to Fix >> Click on Add Column
4. To the Right side >> Click on Date >> Date Only
5. Or Click on Time >> Time Only
6. Remove unwanted columns (right Click >.remove)
7. Click on the Home Tab >> Close and load >. Data Fixed an sent back to Excel

Method 2:

Text To Column

1. select the Column to Fix
2. Data Tab >> Text To Column
3. Step 1 of the Wizard >> Select Delimiter
4. Step 2 >> Select Space
5. Step 3 >> Select MDY (for Date)
6. Select Destination to paste Fixed Data

Hope that helps

Re: Time Conversion Issue

IMHO, both won't convert 123456 into 12:34:56

Highlighted

Re: Time Conversion Issue

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