<aside> <img src="https://s3-us-west-2.amazonaws.com/secure.notion-static.com/a5a1e3ce-00fa-46e4-9a75-58c2aed9e8ce/Notion_Fundamentals_with_Thomas_Frank_-_Avatar_2021.png" alt="https://s3-us-west-2.amazonaws.com/secure.notion-static.com/a5a1e3ce-00fa-46e4-9a75-58c2aed9e8ce/Notion_Fundamentals_with_Thomas_Frank_-_Avatar_2021.png" width="40px" /> This formula returns the number of seconds from a string value in HH:MM:SS format.

Created by Thomas Frank | Learn Notion Formulas | Notion Basics | Templates | Twitter

</aside>

Seconds from Formatted Duration

Full Formula

<aside>

The most complex thing we’re doing here is extracting the duration string from any surrounding context. We do this with the following regex: ^.+?((?:\\d+:)+\\d+).*, which creates a capture group for groups of numbers separated by a : character: https://regex101.com/r/WWpX6F/1

After that, we do the simpler work of ensuring at least 2 digits in each group (HH, MM, SS), and ensuring there is an HH group by adding 00 if not. Finally, multiply each group by a modifier – 3600 for hours, 60 for minutes – and sum up the total. You can check the results here: https://www.inchcalculator.com/seconds-to-time-calculator/

</aside>

lets(
  durationString,
  prop("Duration").replace("^.+?((?:\\d+:)+\\d+).*", "$1"),
  groups,
  durationString.split(":"),
  standardizedGroups,
  groups.map(
    current.toNumber() < 10 ? "0" + current : current
  ),
  standardizedDuration,
  standardizedGroups.length() == 3 ? standardizedGroups.join(":") : "00:" + standardizedGroups.join(":"),
  standardizedDuration.split(":").map(
    ifs(
      index == 0,
      current.toNumber() * 3600,
      index == 1,
      current.toNumber() * 60,
      current.toNumber()
    )
  ).sum()
)

© Thomas Frank