Convert String Duration to HH:MM:SS

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.

Date Time

Preparation

Accepted answers

jrgo

@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

All comments

jrgo

@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.

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.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703