Data Prep Time format

[BarleyCorn]

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

[MarqueeCrew · Accepted solution]

@BarleyCorn,

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

[danrh · Accepted solution]

Another option:

Note that in the Data Cleansing tool I've selected to remove "Leading and Trailing Whitespace" and "Punctuation".

[Syarifhidayat · Accepted solution]

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.

[BarleyCorn]

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

[BarleyCorn]

Sorry all - Replies have gone into my Spam!

I'll look at now.

Cheers

[MarqueeCrew]

You should have noticed that after a few minutes of no solution posts! We’ve got your back.

[MarqueeCrew]

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

[JessicaS]

Hi All,

Just a quick note - I've merged the two threads on this topic.

Thanks!

[BarleyCorn]

Thanks Mark and danrh but for me Syarifhidayat has a pretty good simple solution.

As always really appreciate your help!