Help

This Product Ideas board is currently undergoing updates, but please continue to submit your ideas.

Convert Duration to string

cancel
Showing results forย 
Search instead forย 
Did you mean:ย 
Justin_Barrett
18 - Pluto
18 - Pluto

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:

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.

17 Comments
Airtable_Clerk
6 - Interface Innovator
6 - Interface Innovator

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
18 - Pluto
18 - Pluto

@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
6 - Interface Innovator
6 - Interface Innovator

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

Devan_Kreitzer
6 - Interface Innovator
6 - Interface Innovator

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
18 - Pluto
18 - Pluto

@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
5 - Automation Enthusiast
5 - Automation Enthusiast

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
18 - Pluto
18 - Pluto

@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)
)

Screen Shot 2022-02-10 at 3.55.03 PM