Help

Re: Time Study Data Scenario - Combine like records into 1 that can sum time spent

Solved
Jump to Solution
1863 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Kiersten_Kollin
6 - Interface Innovator
6 - Interface Innovator

Hello,

We are running a time study with our teams and we are bringing our data into Airtable from a time tracking tool.

We are running into a scenario where a designer could have multiple time stamp entries for the same task on a project. The problem is we need to be able to sum up like project task records to get the overall total time they spent doing the task, thus creating a new record with the summed time number.

Is there a way Airtable can do this? I started looking at rollups and formulas and I am not too sure where to start or if I am going down the right path. 

time study data scenario.png

 

 

 

 

 

 

Thank you!

2 Solutions

Accepted Solutions
TheTimeSavingCo
17 - Neptune
17 - Neptune

Yeah this is doable.  Try:
1. Creating a new table and called it "Summary" or something
2. Create a linked field in your original table to the "Summary" table
3. In your original table, copy the values of the "Name" field and paste them all into the linked field to the "Summary" table
4. In your "Summary" table, create a rollup field on the "Duration" field with the formula `SUM(values)`


You should end up with this:

Screenshot 2023-02-24 at 4.26.19 PM.png

Screenshot 2023-02-24 at 4.26.17 PM.png

And here's a link to the base

See Solution in Thread

Ah I see what you mean.  You'll need to create a formula field that combines the task and project names and use that as the linked field value like so:

Screenshot 2023-02-26 at 12.05.51 PM.png

Screenshot 2023-02-26 at 12.05.49 PM.png

I've updated the original base with said functionality

See Solution in Thread

6 Replies 6
TheTimeSavingCo
17 - Neptune
17 - Neptune

Yeah this is doable.  Try:
1. Creating a new table and called it "Summary" or something
2. Create a linked field in your original table to the "Summary" table
3. In your original table, copy the values of the "Name" field and paste them all into the linked field to the "Summary" table
4. In your "Summary" table, create a rollup field on the "Duration" field with the formula `SUM(values)`


You should end up with this:

Screenshot 2023-02-24 at 4.26.19 PM.png

Screenshot 2023-02-24 at 4.26.17 PM.png

And here's a link to the base

Thank you! I will give this a try.

Thank you again for providing the example. 👍

I did re-create this base and got everything to work. I did add in one more field on table 1 for the project number. I am wondering if you happen to know of a way that can group the data by specific projects so the rollups are not clumping like tasks from different projects into one. 

Here is the base link.

I am not sure if is a super complex build in Airtable that may require a script to be able to separate the data out in that way.

 

Glad I could help!

The base link you provided doesn't work I'm afraid and I have requested access

Ah I see what you mean.  You'll need to create a formula field that combines the task and project names and use that as the linked field value like so:

Screenshot 2023-02-26 at 12.05.51 PM.png

Screenshot 2023-02-26 at 12.05.49 PM.png

I've updated the original base with said functionality

Thank you! This worked for my use case.