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.
Welcome to the community, @Zack_McCarty! 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”):
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.
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:
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.
@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.)
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?