Convert String Duration to HH:MM:SS

[DHB]

I've got some durations data as strings which I'm having trouble converting to time values because many of them are over 24 hours.  Is there some format I can convert them to that would allow me to sort them by duration.

Duration

26:42:41

105:53:56

50:08:01

21:22:50

73:42:05

108:08:11

1:55:12

Thank you for your help.

[jrgo · Accepted solution]

@DHB 

Your best bet would be to PADLEFT() the Duration field to a length of 9 padding 0's at the start. This would then allow you to sort the data 

PADLEFT([Duration], 9, '0')

Hope this helps!

Jimmy
Teknion Data Solutions

[black_adder]

Hi DHB,

Put in a text to columns tool, splitting on ":"

Then put in a formula, where you convert them all to minutes (e.g., time = hour*60 + minutes + seconds/60)

Hope this helps

[DHB]

Thank you both for your solutions - I've cracked it now.

[Femi_Olunuga]

Hello 

I have a similar problem and I used the PadLeft, it worked for me but the column is still a string , I want it in date (HH:MM:SS) format so that I can perform other aggregation on it.

How did you go about your, please advise.

[jrgo]

Hi @Femi_Olunuga 

The solution using the PADLEFT() function won't work since the resulting value is not a compatible TIME format. The hour part should would need to be a value between 00 - 23.

If it is between that, you can put a SELECT tool after and just convert it to a "Time" data type field.

If you haven't already solved for this already, I'd suggest submitting a new post in this channel with some samples and what kind of aggregations you're trying to perform on it.

[DHB]

Hi @Femi_Olunuga, sorry for my late reply.  At the time you posted your question my hard drive decided to stop working and took with it six years' work.  Thankfully I managed to recover most of it.  @jrgo's answer covers it. 

I created these string fields;

Days: padleft(tostring(FLOOR([TIME_TAKEN]/(24*3600))),2,"0")

Hours: Padleft(Tostring(FLOOR((Mod([TIME_TAKEN],(24*3600))/3600))),2,"0")

Minutes: Padleft(Tostring(FLOOR(MOD([TIME_TAKEN],3600)/60)),2,"0")

Seconds: Padleft(Tostring(Floor(Mod([TIME_TAKEN],60))),2,"0")

I can sort them based on these four fields and I also concatenated them into another string field ([Days]+" Days "+[Hours]+" Hours "+[Minutes]+" Minutes "+[Seconds]+" Seconds").

Hope that helps a bit.

Best,

DHB.