• 461K Members
• 4,931 Online
• 559K Conversations

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

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

# 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

# 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

# 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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies