Converting Seconds to HH:MM:SS

[DHB]

Hi all,

I'm trying to convert a column of seconds values (int64) into HH:MM:SS and am using this formula;

PadLeft(ToString(Mod([TA_SEC]/3600,3600)),2,"0")+":"+

PadLeft(ToString(Mod([TA_SEC]/60,60)),2,"0")+":"+

PadLeft(ToString(Mod([TA_SEC],60)),2,"0")

it works fine for anything up to 24 hours (86,400 seconds) but for anything that high or above it returns a null.  The highest one I have is 2,604,600 (30 days, 3.5 hours).

Can anyone suggest a way to get a non-null value for all of these?  If the largest one came out as 723:30:00 that would be fine.

Thanks for your help.

Best,

DHB

[RolandSchubert · Accepted solution]

Hi @DHB ,

the good news is - your formula is working fine and only small adjustment should be needed to resolve the problem. I assume, you selected "TIME" as data type for the result. The maximum value for "Time" is "23:59:59". If you select a string data type, you'll see the expected result. If you need the result to perform calculations, (e.g. add the time elapsed to a starting date/time) you'll have to split it to hour/minute/seconds fields of a numeric data type.

Let me know it it works for you.

Best,

Roland

[grazitti_sapna]

Hi @DHB , I have prepared two solutions you can follow any of them you like, please refer to the screenshots and workflow.

Thanks.

-Solution 1

-Solution2

[paulfound]

Bit longer than your formula, but you could break it down in a formula tool? Then you can format as you see fit?

[DHB]

That's great thank you Roland.  I've created the extra fields to perform any calculations too.

[tsmit593]

For anyone else finding this solution down the road, I also tweaked it a bit to include the number of days, it now looks like this:

PadLeft(ToString(Mod([Field_name]/86400,86400)),1,"0")+"d "+
PadLeft(ToString(Mod(Mod([Field_name]/3600,3600),24)),2,"0")+"h "+
PadLeft(ToString(Mod([Field_name]/60,60)),2,"0")+"m "+
PadLeft(ToString(Mod([Field_name],60)),2,"0") + "s"