Simple time tracking per task for multiple assignees

Mike Tomei

I'm trying to devise a simple way for task assignees to track the amount of time it took them to complete the task. I created a report that shows the user their assigned tasks across multiple sheets, allows them to check a box when they've completed the task, but now I want to also have a field in that same report for them to log the amount of time it took them to complete the task. Pretty simple, except for when I have multiple people assigned to the same task. I need each of them to be able to enter their hours for that task, without overwriting the hours that may have already been entered by one of the other assignees. Any suggestions as to how I can make this possible? Thanks.

Devin Lee

Hey @Mike Tomei

How about a time log trigger that copies the row into another sheet that we can then pull from to get total hours? To record that information you need to add three columns [Name], [Time (hours)], and [Log Time] which is a checkbox trigger to preform the automation. I would also add in [Total Time] so when people enter in a new time they can see the number go up and you can also see how many hours that task has been preformed.

Create the automation as seen below. This will record the row and clear the three columns so they can be used again later. I would also make [Time {hours}] a dropdown list with restricted values to the [Total Time] calculation stays consistent.

With this setup the time should be recorded on the other sheet and all you need to do is set up a SUMIF statement in the [Total Time] column.

=SUMIF({Community Help 2 Range 1}, Task@row, {Community Help 2 Range 2})

Range 1 = [Task Name]:[Task Name]

Range 2 = [Time {hours}]: [Time {hours}]

From there you can create another automation in the second sheet that either allows someone to approve the time through an update request or sends an update request to the person submitting the information to confirm it. If either of those are going on I would modify the SUMIF formula to a SUMIFS and include the approval as conditions.