Format string durations in Alteryx to mm:ss format

[rimaissa]

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

[Kenda]

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.

[jdunkerley79 · Accepted solution]

I would suggest:

- 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]

Worked perfectly, thank you!

[JPSeagull]

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:

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!