Early bird tickets for Inspire 2023 are now available! Discounted pricing closes on January 31st. Save your spot!

Alteryx Designer Discussions

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

Format string durations in Alteryx to mm:ss format

rimaissa
6 - Meteoroid

Hi,

Trying to figure out how to format a string duration to hh:ss:

   e.g.  273d 21h 28m 33s

 

How do I get this string field formatted to calculate the number of minutes/seconds in the string example. So I'd need it to look like: 394,408:33 (mm:ss)

 

Another example:

         2h 3m 10s  --> 123:10

 

Any ideas? I've tried parsing the data but not all of the fields have days, hours, minutes, and seconds so the parsing doesn't really work

4 REPLIES 4
Kenda
15 - Aurora
15 - Aurora

Hey @rimaissa 

 

I split up the data field into rows then used a formula + a Summarize tool to do the calculations. See if the attached works for you.

 

Kenda_0-1604953512312.png

 

jdunkerley79
ACE Emeritus
ACE Emeritus

I would suggest:

jdunkerley79_0-1604953702323.png

- Use RegEx tool to parse the string to day, hour, minute, second:

(\d+d)?\s*(\d+h)?\s*(\d+m)?\s*(\d+s)?

- Use a multi-field tool to clean up the d/h/m/s and convert to a number

ToNumber(REGEX_Replace([_CurrentField_],"[dmhs]",""))

- Build the new string:

ToString((24 * [Day] + [Hour]) * 60 + [Minute],0, 1) + ":" + ToString([Second])

 

Have attached a sample

rimaissa
6 - Meteoroid

Worked perfectly, thank you!

JPSeagull
8 - Asteroid

Hi. Been searching for a solution and found your response, but I actually need the opposite. I have total seconds and need to convert it to look like your Field 1:

 

JPSeagull_0-1660144900495.png

How does this work in reverse if I have a column of total seconds and I need to cast to string showing days, hours, minutes and seconds? Thanks!

Labels