Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- Re: Showing Duration field with words

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

0
2236
0

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 11, 2018 11:07 PM

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!

Reply

10 Replies 10

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 12, 2018 12:37 AM

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
```

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 12, 2018 02:46 PM

Oh wow! This is amazing,

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 12, 2018 05:19 PM

I should have mentioned a few things:

- 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. - 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’.) - 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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 23, 2019 12:44 PM

My apologies if reviving this old topic is annoying. Is there a way to drop the minutes’ line if 0 minutes?

Thx.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 07, 2021 01:08 AM

How would you do it in reverse?

Convert 1h 16m to a duration field?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 30, 2021 06:11 PM

Hi there, reviving an old thread but getting an error with your formula - it’s showing 0 minutes if the duration field is equal to one hour:

Thoughts? Thank you! :slightly_smiling_face:

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

May 01, 2022 04:08 AM

Hey @W_Vann_Hall, thank you for this bit of inspiration! I definitely fit into the no/low code brain category, but attempted to use this as a springboard for converting Duration to “X Minutes, and Y Seconds”. All of my Durations are under an hour, so I did not need any If/Then statements for my use case. But for anyone who finds this, here is what i came up with…

(Rounddown((Duration/60),0)) & " minutes, and " &

(Duration-(Rounddown((Duration/60),0)*60))& " seconds"

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Sep 04, 2022 11:50 AM

I have this same problem. I added a = to the formula to see if it is exactly 1 hr. It displays as “1hr, 0mins”. Not sure how to get rid of the 0min section…

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 20, 2023 12:37 PM

This thread was almost exactly what I was looking for except like others mentioned, I wanted a solution to not show 0 minutes. However I was able to take the previous formula and modify it to not include the comma or the second half if it was equal to 0 minutes. Thank you!

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