Sum time values like "m:s" for music/video records


#1

I have records for music tracks of an album, each with a own playtime field as text in the format “m:s”.
I know how to use a formula to convert this values to seconds and the “sum” shows also the correct values of all records in seconds but how can I display this sum value as a formatted text like “h:m:s” to have the complete runtime of all tracks in this collection?

Unfortunately the “sum” function doesn’t have a formatting option, so I need something different, but I have completely no idea how to do this.


#2

I suggest you us a Duration field for this - either just convert the field and edit it (I think it would be interpreted as hours and minutes) or:

  1. Create a formula field with the formula “0:” & {playtime)

  2. This should give you 0:4:32 for example. Convert this field to a Duration field formatted as h:mm:ss and you should end up with what you need.

Hope this helps.


#3

This doesn’t solve this problem it only re-formats the string.

I found a different solution with help of a formula field which parses the string to seconds. I linked the track records to the table with the album record, use a hidden “rollup” field to sum the total seconds and a formula to represent this summed seconds as a string in the format “h:m:s”.

I think for such behavior it needs a own “duration” field with some different options how the duration should be interpreted/formatted. For all kind of video/audio this is a needed field, so hopefully it will be implemented soon.


#4

Airtable is currently beta-testing a Duration field much as you describe, but there was some confusion earlier [largely thanks to me] as to whether this was beta or in general release. (If it doesn’t appear as an option under ‘Customized field type’ when you right-click on a field, it’s evidently not yet GA.)


#5

Aah, ok… so lets wait if the beta becomes a release :wink: I hope it doesn’t take years :stuck_out_tongue_winking_eye:


#6

My apologies Frank - I’d forgotten that the Duration field was still a beta item!!