Question

I have a database with a text property called Time Spent. I need to convert the text to minutes.

Time Spent
2:12
00:20 | 1:45
1:34 & 2:34 & 3:00
00:45 - 1:11 - 1:33 - 00:12

So far, I can only convert the first row to minutes using this formula:

toNumber(prop("Time Spent")) * 60 + toNumber(replace(prop("Time Spent"), "[^ ]:", ""))

replaceAll(prop("Time Spent"), "[&|-]", "")

removes the strings/characters that I want between the hours:minutes but I'm still stuck on how to convert after removing those characters.

I really appreciate any help you can provide.

Answer 1

So I think there are 2 reasons this hasn't been answered:

1. It's not possible in Notion to iterate through an Array, or List, and then sum() the values in the Array / List.
2. More importantly, this is NOT how a RDBM (Relational Database Model) is properly structured and it's definitely not scalable even if you could do it in Notion.

Let's assume you have Projects, Project Tasks, and Time Spent doing those Tasks. This should be constructed in the following RDBM way:

• Each Project can have many Tasks.
• Each Task may have many "Time Spent" items. For example, a Task may not be able to be completed in a single hour, day, or week.

So you would structure this so Projects are related to Tasks.
And Tasks are related to Time Spent entries.
Then you can use Rollups and Formulas to sum the amount of time a Task took...AND...the total amount of time a Project took.

It was easier and faster for me to just create the entire system than it would be to explain it.

You can duplicate the following page to your own workspace and see how it works:
https://shanerobinson.notion.site/Tracking-Time-Spent-Example-7ba4df524f434ac9b2c6df8758c8d9a6?pvs=4