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: SUM of several MINUTES and transform the sum i...

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

2
2864
1

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

May 26, 2021 08:16 AM

Hello everyone!

I’ve got several cols (days of the week) that have a time that I wasted in doing a task in minutes (ex: 120). All that I’m trying to do is a SUM of this minutes but in a h:mm format.

Example: The task is “Book Design” and the minutes I’ve spent in doing that task are (120, 120, 60, 60, 30). The SUM of that minutes is 390… that’s 6:30 hours. All I want to have is this number 6 and I don’t know how to get it.

Thanks in advance!

Solved! Go to Solution.

1 Solution

Accepted Solutions

Solved
See Solution in Thread

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

May 26, 2021 10:01 AM

Welcome to the Airtable community!

One hour is 60 minutes, so 360 minutes is 6 hours (360 / 60). I’m not sure how you are getting 6 hours and 30 minutes.

How to get the sum of 6 hours depends on the field types of the initial values of minutes. If they are number fields, you can sum the minute fields and divide by 60.

For example, `SUM({Monday}, {Tuesday}, {Wednesday}, {Thursday}, {Friday}) / 60`

On the other hand, if your minute fields are duration fields (which sounds unlikely), you should know that duration fields are stored internally as a number of seconds, so you will need to convert seconds to hours. 1 hour = 3600 seconds.

If you want to format a numeric result as a duration in the formula settings, also keep in mind that the formula should calculate a number of seconds (not minutes).

Reply

4 Replies 4

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

May 26, 2021 08:33 AM

Airtable doesn’t allow formatting of number fields as durations (which is strange), but it DOES allow the formatting of formula fields as durations.

So just create a formula field that equals the number of minutes that you’re looking to display, and then format the field as a duration.

Solved
See Solution in Thread

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

May 26, 2021 10:01 AM

Welcome to the Airtable community!

One hour is 60 minutes, so 360 minutes is 6 hours (360 / 60). I’m not sure how you are getting 6 hours and 30 minutes.

How to get the sum of 6 hours depends on the field types of the initial values of minutes. If they are number fields, you can sum the minute fields and divide by 60.

For example, `SUM({Monday}, {Tuesday}, {Wednesday}, {Thursday}, {Friday}) / 60`

On the other hand, if your minute fields are duration fields (which sounds unlikely), you should know that duration fields are stored internally as a number of seconds, so you will need to convert seconds to hours. 1 hour = 3600 seconds.

If you want to format a numeric result as a duration in the formula settings, also keep in mind that the formula should calculate a number of seconds (not minutes).

Reply

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

May 27, 2021 08:31 AM

Thanks for the correction, @kuovonne! Yes, @Rafa_Ramos — I meant seconds, not minutes.

Reply

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

May 28, 2021 06:10 AM

Finally I made a formula field like this: SUM({Monday}, {Tuesday}, {Wednesday}, {Thursday}, {Friday}) * 60 and I assigned the formatting (format: duration, duration format: h:mm).

Example: Monday=1440, Tuesday=30, and the other days are equal to 0. Result=(1440+30)*60=1470*60=88200. This number (88200) will be shown as a duration field (h:mm). The result is 24:30

Reply