format text to HH:MM:SS based on condition

[kaylendao]

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. 

[kaylendao]

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]

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

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

[adudani · Accepted solution]

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.