Hi Alteryx Community,
Its been a while, happy 2018!
I have a time formatting issue that I hope you can help with?
I have a data set with a time format that varies randomly through the database set as follows:
Time
10:02:03
11:05:01
742
1215
23:30
21:05
22:52:01
2252
09:01:02 ( 2 spaces in front)
10:02:22
10:01:55
I would like to transform to consistent format of hh:mm:ss for entire column.
Thanks
Solved! Go to Solution.
Quick Solve with 1 formula tool & a few formulas. You can nest it into 1 formula or use 1 formula with IF THEN ELSEIF... But the underlying logic is:
Time_out (String) is:
Trim([Time])
...
iif(REGEX_Match([Time_out], "\d+:\d+:\d+"),[Time_out], REGEX_Replace([Time], "(\d{2}):(\d{2})", "$1:$2:00"))
...
iif(REGEX_Match([Time_out], "\d+:\d+:\d+"),[Time_out], REGEX_Replace([Time], "(\d{2})(\d{2})", "$1:$2:00"))
...
iif(REGEX_Match([Time_out], "\d+:\d+:\d+"),[Time_out], REGEX_Replace([Time], "(\d{1})(\d{2})", "0$1:$2:00"))
...
iif(REGEX_Match([Time_out], "\d+:\d+:\d+"),[Time_out], REGEX_Replace([Time], "(\d):(\d+)", "0$1:$2:00"))
Now use a select tool to convert to TIME data type.
Cheers,
Mark
Another option:
Note that in the Data Cleansing tool I've selected to remove "Leading and Trailing Whitespace" and "Punctuation".
hey @BarleyCorn,
Here is my suggestion option for your problem.
First, you need Data Cleansing Tool to remove ':' and leading space.
Then you need Formula Tool to check:-
if length=3 then '0'+[Time]+"00"
if length=4 then [Time]+"00"
if length=6 then [Time]
Final result you will get in format HH:MM:SS. Take a look at my workflow to get better understanding.
Hi
I posted this question yesterday - but got no replies and seems now not to be on Community??
I have a time format issue.
I would like to format a time variable that has an inconsistent format in the input excel as follows:
10:20:01
12:52:01
754
1252
13:01:02
10:05 (two spaces in front)
11:30
12:55:01
923
I would like to transform into column in HH:MM:SS format.
Many thanks
Sorry all - Replies have gone into my Spam!
I'll look at now.
Cheers
Tom,
Welcome to the community might be the issue. Posts like this should go to Data Preparation & Blending. A moderator has likely moved the post on your behalf.
https://community.alteryx.com/t5/Data-Preparation-Blending/Data-Prep-Time-format/m-p/117538#M20128
You've got many replies.
Cheers,
Mark
Hi All,
Just a quick note - I've merged the two threads on this topic.
Thanks!
Thanks Mark and danrh but for me Syarifhidayat has a pretty good simple solution.
As always really appreciate your help!