Showing Duration field with words


#1

I’m looking for a way to take a duration field (eg 1:32) and show it as “1 hours, 32 minutes” like with text.

I’m imagining there might be a formula I could use, but I’m not sure the best way to go about it.

Any thoughts would be great!


#2

Try

IF(
    {Duration}>3600,
    INT(
        {Duration}/3600
        )&
    ' hour'&
    IF(
        {Duration}/3600 >= 2,
        's'
        )&
    ', '
    )&
INT(
    MOD(
        {Duration},
        3600
        )/60
    )&
' minute'&
IF(
    OR(
        (MOD(
            {Duration},
            3600
            )/60)>=2,
        MOD(
            {Duration},
            3600
            )=0
        ),
    's'
    )

Here’s the output from my test run:

1 hour, 23 minutes
34 minutes
1 hour, 45 minutes
1 hour, 32 minutes
1 hour, 27 minutes
1 hour, 44 minutes
1 hour, 56 minutes
2 hours, 0 minutes
1 hour, 34 minutes
1 hour, 45 minutes

#3

Oh wow! This is amazing,

Thank you so much. you’ve just taught me some awesome new formula functions too.


#4

I should have mentioned a few things:

  1. The reason for the '3600's throughout the formula is that, internally, Airtable stores duration fields by the number of seconds. Since you were working with h:mm durations, I divided by and, um, moduloed by 3600, the number of seconds in an hour, to break out hours and minutes.
  2. As the example output showed, this formula drops the entire hour clause for durations < 60 minutes. If you’d prefer it to read '0 hour, ## minutes', eliminate the opening IF() statement. (If you prefer '0 hours', you’ll also need to preface an OR() statement, similar to the one in the minute section, to the IF() statement determining whether or not to append and ‘s’ to ‘hour’.)
  3. The INT() function surrounding the number of minutes might not be necessary, as presumably Airtable would store a value entered into an 'h:mm'-formatted duration field as calculated based upon integer hours + integer minutes. But how does it handle an attempt to enter '1:23.4' in that field? Or '1:23:24'? Or if you reconfigure an existing 'h:mm:ss' field to 'h:mm'? Rather than take the trouble to find out, I took the easy way out and wrapped it in an INT().

Yeah, it’s worth taking the time every few weeks to skim back through the Airtable Formula Field Reference, just to see if anything new wants to stick to your brain. Speaking personally, just as there are functional areas within Airtable I’ve never used in my life (commenting, for instance), there are formula functions and operations I rarely touch; when I need them, all too often I end up using what I do know to build the same functionality… at the cost of extraneous fields and processing steps.

It’s also worthwhile to scan through the list of format specifiers for DATETIME_FORMAT() and DATETIME_PARSE() every so often. It’s amazing how many ways the Unix community has managed to find to answer the simple question, ‘pardon me, but do you have the time?’ People sometimes jump through any number of hoops to arrive at a value DATETIME_FORMAT() would be happy to provide for free.