Format string durations in Alteryx to mm:ss format

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

Transformation

Accepted answers

jdunkerley79

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

Duration Format.yxmd

All comments

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.

rimaissa.yxmd

jdunkerley79

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

Duration Format.yxmd

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!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703