Turn on suggestions

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

Showing results for

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- How to convert duration field into into a HH:mm:ss...

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

Solved

Jump to Solution

0
2804
3

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

Jan 04, 2023 06:38 AM

This seems like something I should have been able to figure out, but I've been struggling to come up with a solution.

Duration fields are handy for inputting data, displaying data, and manipulating it in Airtable. But the underlying data is the number of seconds in a text string. For example, one hour, seven minutes, and seven seconds will show as 1:07:07 but the underlying data is "4027".

When I use the output via automation, the field output is 4027. I want to use 1:07:07.

Does anyone know a formula or automation that I can set up to convert seconds into a HH:mm:ss text output?

Solved! Go to Solution.

2 Solutions

Accepted Solutions

Solved
See Solution in Thread

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

Jan 04, 2023 10:26 AM

See if this clears it up. ("test" is the duration field).

IF(test,

CONCATENATE(

INT(test/3600), " hours " ,

INT(MOD(test,3600)/60)," minutes ",

MOD((test-3600),60), " seconds " ),

"")

Reply

Solved
See Solution in Thread

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

Jan 09, 2023 06:37 AM

I made another edit so that it can work with a duration of longer than an hour and less than an hour. For example, you will get 1:30:00 for an hour and a half, but only 30:00 for a half hour. You also don't need the -3600 in the last couple of sections.

```
IF({Video Length}>3600,
CONCATENATE(
INT({Video Length}/3600),
IF(INT(MOD({Video Length},3600)/60)<10,":0",":"),
INT(MOD({Video Length},3600)/60),
IF( MOD(({Video Length}),60)<10,":0",":"),
MOD(({Video Length}),60) ),
CONCATENATE(
INT(MOD({Video Length},3600)/60),
IF(MOD(({Video Length}),60)<10,":0",":"),
MOD(({Video Length}),60)
)
)
```

3 Replies 3

Solved
See Solution in Thread

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

Jan 04, 2023 10:26 AM

See if this clears it up. ("test" is the duration field).

IF(test,

CONCATENATE(

INT(test/3600), " hours " ,

INT(MOD(test,3600)/60)," minutes ",

MOD((test-3600),60), " seconds " ),

"")

Reply

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

Jan 04, 2023 12:17 PM

Thank you, I haven't worked with MOD before. I see how that works and will need to work with it some more so that I think of it next time. I made a bit of a change just to keep the 0:00:00 format.

CONCATENATE(

INT({Total Video Length}/3600),

IF(INT(MOD({Total Video Length},3600)/60)<10,":0",":"),

INT(MOD({Total Video Length},3600)/60),

IF( MOD(({Total Video Length}-3600),60)<10,":0",":"),

MOD(({Total Video Length}-3600),60) )

Reply

Solved
See Solution in Thread

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

Jan 09, 2023 06:37 AM

I made another edit so that it can work with a duration of longer than an hour and less than an hour. For example, you will get 1:30:00 for an hour and a half, but only 30:00 for a half hour. You also don't need the -3600 in the last couple of sections.

```
IF({Video Length}>3600,
CONCATENATE(
INT({Video Length}/3600),
IF(INT(MOD({Video Length},3600)/60)<10,":0",":"),
INT(MOD({Video Length},3600)/60),
IF( MOD(({Video Length}),60)<10,":0",":"),
MOD(({Video Length}),60) ),
CONCATENATE(
INT(MOD({Video Length},3600)/60),
IF(MOD(({Video Length}),60)<10,":0",":"),
MOD(({Video Length}),60)
)
)
```