Forum Discussion

kaylendao's avatar
kaylendao
Regular Visitor
3 years ago
Solved

format text to HH:MM:SS based on condition

Hi there, I have a text column that contains "None" and time in HH:MM:SS, but sometimes the data submitted is incorrect such as H:MM:SS, instead of the correct HH:MM:SS format.  I would like a write a formula in Excel power query (perhaps a custom column) to correct the time format with one condition:

if the text in column [time of request] = None, then None, other wise format the text in [time of request] to HH:MM:SS. 

 

Any help is appreciated. 

  • hi kaylendao ,

     

    add a custom column and paste the following code:

     

    if [time of request] = "None"   then "None" else if Text.Length([time of request]) =8 then [time of request] else "0"&[time of request]

     

    This concatenates  0 infront of the values that don't have a "HH:MM:SS" format.

     

4 Replies

  • adudani's avatar
    adudani
    Memorable Member

    hi kaylendao ,

     

    add a custom column and paste the following code:

     

    if [time of request] = "None"   then "None" else if Text.Length([time of request]) =8 then [time of request] else "0"&[time of request]

     

    This concatenates  0 infront of the values that don't have a "HH:MM:SS" format.

     

  • kaylendao's avatar
    kaylendao
    Regular Visitor

    Thank you for the great idea. But now that I think about it, if the data is showing 8:30:10, we can't really assume that it is supposed to be 08:30:10 since it could be 18:30:10. So I guess I won't be able to apply this logic. 

    • adudani's avatar
      adudani
      Memorable Member

      kaylendao 

      Please provide sample input data removing sensitive data, for various different cases and I'll be happy to try and adapt a formula that could fit the requirement.

      If there is an indicator for AM/PM any other reference columns as well?

      • kaylendao's avatar
        kaylendao
        Regular Visitor

        I tried the formula you suggested and it put a 0 in front of the None and not the 8:32:00