Forum Discussion
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
- adudaniMemorable 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.
- kaylendaoRegular 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.
- adudaniMemorable Member
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?
- kaylendaoRegular Visitor
I tried the formula you suggested and it put a 0 in front of the None and not the 8:32:00