Convert Duration to string

[Justin_Barrett]

Duration fields are awesomely useful. However, if one wants to simply transfer the displayed duration (e.g. 1:45) to another field via a formula, Airtable passes along the stored value in seconds. Converting that back into a duration-like display is doable, but a little tricky, as I found out in this recent discussion:

Convert Seconds to h:mm in forumla Ask the community

I’m having trouble with something that seems very simple. I’d like where it says “0.9 available” for it to show that time formatted as “h:mm”, as it does in the “Time Available” field next to it. [image] Here the formula for the Name field: [image] Essentially I think I need to convert seconds to hours, minutes and seconds within a formula, but I can’t find a way to do it and feel I’m probably just missing something simple. Thanks so much.

I tried the TIMESTR() function, but that just gave an error, so I ended up writing my own seconds-to-duration formula.

My suggestion: expand the TIMESTR() function’s capabilities to also convert durations into strings. I suppose a new function named something like DURSTR() might also work, but because durations are all about time, wrapping that conversion into TIMESTR() feels like it makes more sense.

[Artem_Bourov]

Very much agreed.

As a workaround, I tried a formula too. I’m sure there is a simpler version but this seemed OK.

ROUNDDOWN({Duration}/3600, 0)
& “:” & IF(MOD({Duration}/60,60)<10,“0”&MOD({Duration}/60,60),MOD(Duration}/60,60))

[Justin_Barrett]

Good job on the formula. Looking back at when I wrote the initial post, that must’ve been really really close to when I first started using Airtable. Nowadays I’d do pretty much what you wrote. :slightly_smiling_face:

[Zack_McCarty]

Took the base of this formula and made something that looks a little cleaner:

IF(ROUND(MOD({Duration}/60,60))<10,ROUNDDOWN({Duration}/3600,0)&":"&“0”&ROUND(MOD({Duration}/60,60)),IF(ROUND(MOD({Duration}/60,60))=60,ROUNDUP({Duration}/3600,0)&":"& “00”,ROUNDDOWN({Duration}/3600,0)&":"& ROUND(MOD({Duration}/60,60))))

[Justin_Barrett]

Welcome to the community, @Zack_McCarty! :grinning_face_with_big_eyes: I tried your formula as well as the one above, and they output exactly the same thing. Here’s the comparison, with yours in the middle, and the one from @Artem_Bourov on the right.

On the left is one that I wrote a couple months ago (after completely forgetting about this thread), which uses the following formula (my duration field is named “Time”):

IF(
    Time,
    FLOOR(Time / 3600) & ":" & REPT("0", 2 - LEN((MOD(Time, 3600) / 60) & "")) & (MOD(Time, 3600) / 60)
)

They’re all identical, so I’m not sure what’s cleaner about yours, unless the difference is only evident with specific values.

[Zack_McCarty]

Hi @Justin_Barrett!

when I used the formula at the top, the Mod formulas were showing many decimals, so I simply added the round formulas to ensure that there were no extra decimals showing.

Apologies if that came across negatively! Maybe this was something specific to my machine.

[Justin_Barrett]

Thanks for the clarification. Duration fields can store fractions of seconds internally, and that might show up in the final formatted version depending on the source of your duration data. However, the duration field’s formatting defaults to rounding the display to whole values. For me (and I’m guessing for @Artem_Bourov as well), the duration will never be fractional, so we never hit that issue when building the final string version, and the shorter formulas are sufficient. It sounds like your duration data often contains fractional values, so that longer formula makes more sense. My gut says that there’s probably a way to make it more compact, but I don’t have time at the moment to look for possible ways to optimize it.

[Airtable_Clerk]

Thanks for the formulas! I needed to convert a duration to a 12 hour AM/PM format with seconds added. Example: duration of zero produces “12:00:00 AM”. Here’s my formula based on the earlier topics in this conversation:

IF(Time,

IF(Time <3600,"12",IF(ROUND(Time / 3600)>12, ROUND(Time / 3600)-12, ROUND(Time / 3600)))

& ":" & REPT("0", 2 - LEN(ROUND(MOD(Time, 3600) / 60) & "")) & ROUND(MOD(Time, 3600) / 60)

& ":" & REPT("0", 2 - LEN(ROUND(MOD(Time, 60)) & "")) & ROUND(MOD(Time, 60))

& IF(ROUND(Time / 3600)>12," PM"," AM")

)

[Justin_Barrett]

@Airtable_Clerk Thanks for sharing! That formula could be simplified quite a bit:

DATETIME_FORMAT(
    DATEADD(
        DATEADD(
            TODAY(),
            ROUNDDOWN(Time / 3600, 0),
            "hours"
        ),
        MOD(Time, 3600) / 60,
        "minutes"
    ),
    "hh:mm:ss a"
)

This works by taking advantage of the fact that the time portion of TODAY() is always going to be midnight. Add the hours to that time, then add the minutes, then format it.

[Airtable_Clerk]

Justin_Barrett:

DATETIME_FORMAT(
    DATEADD(
        DATEADD(
            TODAY(),
            ROUNDDOWN(Time / 3600, 0),
            "hours"
        ),
        MOD(Time, 3600) / 60,
        "minutes"
    ),
    "hh:mm:ss a"
)

Way more simple. I need to handle negative durations and seconds, but I trashed my old code. Thanks!

[Justin_Barrett]

Airtable_Clerk:

I need to handle negative durations and seconds

In what sense? Could you give an example?