community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Data Prep Time format

Asteroid

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

Alteryx Certified Partner
Alteryx Certified Partner

@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

 

 

 

 

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Pulsar

Another option:

 

image.png

 

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

Highlighted
Alteryx Partner

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.

Asteroid

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

 

 

 

Asteroid

Sorry all - Replies have gone into my Spam!

 

I'll look at now.

 

Cheers

 

 

Alteryx Certified Partner
Alteryx Certified Partner
You should have noticed that after a few minutes of no solution posts! We’ve got your back.
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Alteryx Certified Partner
Alteryx Certified Partner

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

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Moderator
Moderator

Hi All,

 

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

 

Thanks!

Jess Silveri
Premium Support Advisor | Alteryx
Asteroid

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

 

As always really appreciate your help!  

Labels