In need of duration (MM:SS) to do calculations

Ask for Help
1

I have a table of songs and their durations. How can i display this as MM:SS and then summarize those durations to show as a summary of how long a “playlist” is.

2

I’ve done this before but don’t remember the exact formulas. So I am cheating a bit here by letting ChatGPT answer :slight_smile: (with some edits…)

———

In Grist, you can use column-wide Python formulas to compute on your data. If you have durations in MM:SS format in Grist and want to sum them, here’s a method leveraging Grist’s Python capabilities:

  1. Convert MM:SS to Seconds:

    1. Suppose your durations are in a column named Duration.
    2. Create a new column, say Seconds. Set its formula to:
      minutes, seconds = map(int, $Duration.split(":"))
return minutes * 60 + seconds

  2. Sum the Seconds:

    • Add a summary table to get the sum of Seconds column (total, or grouped by something).

  3. Convert the Sum Back to MM:SS Format:

    1. Create another column, say TotalDuration, at the position where you want to display the total duration in MM:SS.
    2. Set its formula (for the cell where you want the total) to:
      total_seconds = $Seconds
return f"{total_seconds // 60}:{total_seconds % 60:02}"
3

Awesome thanks so much, after using that i was able to use AI to get my total per playlist. very cool.

Thanks.

4

Thread resurrection - in case anyone else is looking.

I have some fitness data which comes from various sources (Strava, Keiser) - the times are either in seconds OR as a string duration.

From the above (thanks @Dmitry_Sagalovskiy ) I was able to convert as follows

#1 Strava from Moving time (in seconds) to a string formatted as a duration

m, s = divmod($Moving_Time, 60)
h, m = divmod(m, 60)
f’{h:02.0f}:{m:02.0f}:{s:02.0f}’

#2 Keiser from a duration string to a seconds integer

hours, minutes, seconds = map(int, $Duration.split(“:”))
hours * 3600 + minutes * 60 + seconds

Starting to fall in love with Grist already!

1 Like
5

So #1 works in Grist Electron (where I’ve been testing), no problem, but fails when I import into either self hosted or a hosted instance.

I get the following error

Traceback (most recent call last):
  File "grist/engine.py", line 987, in _recompute_one_cell
  File "usercode", line 288, in Duration_Calc
    raise SyntaxError('invalid syntax', ('usercode', 3, 39, u"return f'{h:02.0d}:{m:02.0d}:{s:02.0d}'"))
  File "usercode", line 3
    return f'{h:02.0d}:{m:02.0d}:{s:02.0d}'
                                          ^
SyntaxError: invalid syntax
6

This looks like a Python version issue. In the hosted version, you can switch versions in Document Settings.

Some more context in this thread.

7

@nick

Yeah, I guessed that AFTER Iposted here (DOH!).

Line #3 needs to be
'{:02d}:{:02d}:{:02d}'.format(h, m, s)'

8

@Martyn_Shiner that works, but I’d encourage you to also switch the Python version to 3 in the hosted or self-hosted version. We’ll be fixing this soon to happen automatically.

9

Thanks @paul-grist - I’ve done that on my self hosted