Seeking assistance with Duration type column

AppSheet AppSheet Q&A
,
1

I’m trying to divide a Duration value in half, and I need the output to be in proper duration format (hh:mm:ss) — not total seconds or a decimal. I’ve tried using expression:

(TOTALSECONDS([Duration]) / 2 / 86400.0) + “000:00:00” R****eturns 0:00:00

I have also tried:

(“000:00:00” + (TOTALSECONDS([Duration]) / 2 / 86400.0)) Returns blank

Additionally, I’ve also tried a few other variations like multiplying by “01:00:00”, but AppSheet keeps throwing type errors (Time vs Duration, etc.). I need this for a virtual column that will be used in an automation.

2

Unfortunately there are no mathematical functions you can use on Duration types. You can convert into a time component, perfomr the calculation and then “reconstruct” the Duration type on the result.

For instance, if you used TOTALHOURS and performed computations on the hours result, then this expression below will construct a Duration type from the “Final Hours” result:

CONCATENATE(
    MOD(FLOOR([Final Hours]), 24),
    ":",
    IFS(
        FLOOR(MOD(((60 * 60) * [Final Hours]), (60 * 60)) / 60) <= 0, "00",
        FLOOR(MOD(((60 * 60) * [Final Hours]), (60 * 60)) / 60) < 10, 
                    "0" & TEXT(FLOOR(MOD(((60 * 60) * [Final Hours]), (60 * 60)) / 60)),
        TRUE, FLOOR(MOD(((60 * 60) * [Final Hours]), (60 * 60)) / 60)
    ), 
    ":00"
)

If you use some other time component, you will need to adjust the CONCATENATE(0 expression to those units.

I hope this helps!

3

Thank you for replying to my post. Unfortunately, a concatenate expression will not work for my needs because it will return a text string and I need it to be duration type for use in calculations, however, your expression did point me in the right direction and I was able to get the needed output using expression:

RIGHT(“00” & FLOOR(TOTALSECONDS([Duration]) / 2 / 3600), 2) & “:” &
RIGHT(“00” & MOD(FLOOR(TOTALSECONDS([Duration]) / 2 / 60), 60), 2) & “:” &
RIGHT(“00” & MOD(TOTALSECONDS([Duration]) / 2, 60), 2)

Thank you again for your help!

1 Like