Converting Seconds to HH:MM:SS

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

Date Time

Transformation

Preparation

Accepted answers

RolandSchubert

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

All comments

RolandSchubert

Hi @DHB ,

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

Datetimestring.yxmd

paulfound

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

Annotation 2020-07-13 071654.png

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"

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335