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.

[Airtable_Clerk]

This is a Broadcast Automation application where the problem domain is down to the second. I’m doing time calculations where some of the duration fields represent the time (duration since midnight) and some are actual durations. Sometimes actual durations are negative.to schedules things before a certain time.

[Justin_Barrett]

@Airtable_Clerk Duration fields support negative values, and you can use those in formulas do calculate offsets. Will that work for your use case? (My apologies if I’m misunderstanding something. I’m still not sure if your comment above was a request for assistance, or just a comment sharing how you’re using durations.)

[Airtable_Clerk]

It was just a sharing comment. I’m already using durations and calculating with negative offsets. Thanks though!

[Devan_Kreitzer]

I have a duration field that is using the DATETIME_DIFF() function. These are for calls so I’d need to know the duration in hh:mm:ss. I know there is the built in formatting feature to change to a duration, but this duration field is being exported into a different program.

If I wanted to change the created hh:mm:ss duration into a string, how would I do that?

[Justin_Barrett]

@Devan_Kreitzer The formulas above will only handle hours and minutes. Here’s one that handles seconds (change all references to “Time” to point to your duration field):

IF(
    Time,
    FLOOR(Time / 3600) & ":" &
    RIGHT("0" & ROUNDDOWN(MOD(Time, 3600) / 60, 0), 2) & ":" &
    RIGHT("0" & ROUND(((MOD(Time, 3600) / 60) - ROUNDDOWN(MOD(Time, 3600) / 60, 0)) * 60, 0), 2)
)

[Georg_Kuklick]

How do need to adjust the formula to avoid having strings like 0:00:14 or 0:05:34

I would need them like this:
1:59
19:59
1:59:59

[Justin_Barrett]

@Georg_Kuklick This modification will work:

IF(
    Time,
    IF(FLOOR(Time / 3600), FLOOR(Time / 3600) & ":") &
    RIGHT("0" & ROUNDDOWN(MOD(Time, 3600) / 60, 0), IF(NOT(FLOOR(Time / 3600)), MIN(LEN(ROUNDDOWN(MOD(Time, 3600) / 60, 0) & ""), 2), 2)) & ":" &
    RIGHT("0" & ROUND(((MOD(Time, 3600) / 60) - ROUNDDOWN(MOD(Time, 3600) / 60, 0)) * 60, 0), 2)
)