Calculating time difference in HH:MM:SS but getting error messages

[Emmm]

Hi there! Can someone please help me take a look at the workflow what went wrong and how to fix it? 

[davidskaife]

Hi @Emmm 

Try tweaking the formula to this: 

TOSTRING(FLOOR([TimeDiffSeconds] / 3600)) + ":" +
RIGHT("0" + TOSTRING(FLOOR(([TimeDiffSeconds] / 3600) / 60)), 2) + ":" +
RIGHT("0" + TOSTRING([TimeDiffSeconds] / 60), 2)

All i did was wrap the first Floor calc in TOSTRING, and you were using % instead of / for the other two calculations

[Emmm]

Thanks @davidskaife 

but in this way, if I use / instead of %, the results were wrong as you can see in the screenshot below. It's not calculating the interval properly...

[davidskaife]

Hi @Emmm 

Just noticed that the final output wasn't working; thanks to the solutions posted here -> https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Calculate-time-hours-minutes-s... see below for a much cleaner formula

RIGHT(DateTimeAdd("1900-01-01",[TimeDiffSeconds],"seconds"),8)

[Emmm]

Thank you so much! @davidskaife 

Now i'm really close - for cases like these crossing dates interval, do you know how can i get the right interval? 

[Raj]

@Emmm 
find the workflow attached is this the result you are looking for?


mark the solutions attached if solved.

[CoG · Accepted solution]

It seems that a mathematical error has been propagating through various responses. The only error in the OP's formula was the use of the '%' to perform modulus. You need to use the MOD() function to perform the appropriate calculation.

TOSTRING(FLOOR([TimeDiffSeconds] / 3600)) + ":" +
RIGHT("0" + TOSTRING(FLOOR(MOD([TimeDiffSeconds], 3600) / 60)), 2) + ":" +
RIGHT("0" + TOSTRING(MOD([TimeDiffSeconds], 60)), 2)

Happy Solving!

[cjaneczko]

Another formula to try.

PadLeft(ToString(Floor([TimeDiffSeconds]/3600)),2,"0") +
Right(DateTimeAdd("1900-01-01",[TimeDiffSeconds],"seconds"),6)

[Emmm]

Thanks everyone!!!!