Forum Discussion

Frost2FA's avatar
Frost2FA
Copper Contributor
Jul 18, 2019

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.

 

Formulas and Functions

6 Replies

  • nabilmourad's avatar
    nabilmourad
    MVP
    Jul 23, 2019

    Frost2FA 

    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

    Nabil Mourad

     

      • nabilmourad's avatar
        nabilmourad
        MVP
        Jul 23, 2019

        SergeiBaklan 

        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

  • Roger Govier's avatar
    Roger Govier
    MVP
    Jul 18, 2019

    Frost2FA 

    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.

    • Frost2FA's avatar
      Frost2FA
      Copper Contributor
      Jul 18, 2019

      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.

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Jul 23, 2019

        Frost2FA 

        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

Resources