Discover what data silos are costing your org in our commissioned Forrester study. Learn more
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.
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).
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.
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).
May 27, 2021 08:31 AM
Thanks for the correction, @kuovonne! Yes, @Rafa_Ramos — I meant seconds, not minutes.
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=147060=88200. This number (88200) will be shown as a duration field (h:mm). The result is 24:30