Formula for total time spent with a specific Single Select field active

Hey twoodl, I think the only way we can do this is via a script I’m afraid. If you’re on a Pro plan we could put it in an automation, if not, we’d be have to make a button for you to click and have update the record (or all records if you’d like)

Let me know if this is something you’d like to explore and I can put something together for you

I’m investigating this issue with the Pro plan in mind. I’m still on the Pro plan trial, so I’d love to hear how automations could accomplish this!

Roger that, let me put something together for you real quick

Hey twoodl, I’ve put something together for you here

The “Time Spent” numbers in the gif are so large as it’s outputting milliseconds; I did that to make it obvious to us that the numbers had changed. I’ve since updated the code to use minutes instead.

You can view the code and automation set up via clicking the automations button at the top right

The idea is that, every time we change the single select field, the automation runs and does the following:

1. Updates the appropriate Last changed to field with the current date and time

• For example, if we’re changing it to Worker now, we’ll update the Last changed to Worker field with the current date and time

2. Looks through all the Last changed to fields to see which one has the latest value. We then assume that the one with the latest value is the single select option selected before this one

• Consider the following example:
  - Last changed to Worker has a date time of today, 1:00 pm
  - Last changed to Reviewer has a date time of today, 1:01 pm
  - We have just changed the single select field to Manager
• Since Last changed to Reviewer has the latest timing, we assume that, before we changed it to Manager, it was set to Reviewer

Another option would be to have a table entirely dedicated to historical data which we use to calculate the time spent for from

Hey Adam_C

Thanks so much for putting that together - I can’t test it because “you cannot run scripts on the current billing plan” but it looks as though it would do exactly what I need.

Could you confirm something for me? Does this automation trigger the Apps sidebar to popup on the right side of the screen every time it runs? Or does the automation just run non-invasively in the background?

Hey twoodl, no worries!

It’ll run in the background and will not trigger the Apps sidebar to popup

Hello @TheTimeSavingCo, 

I stumbled upon this post that seems to have a solution for what I am looking to do as well. However, I do not need the different status for time in Review and Manager. I am looking for a way to track the duration (in minutes, cumulatively - as in if switched in or out of the status it will keep track) a record stays in the "Working" status. I think the statuses I want are "Ready", "Working", and "Paused". I suppose there is some value to tracking the duration it spends in "Paused" as well. Is it possible for you to assist in updating the script you provided above to match my use case? 

Thank you so much