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

[twoodl]

I’ve got a bit of a puzzle which needs cracking here.

I have a table in which each record is used to track jobs in my business.
I have a Single Select field which tracks the job status of each record.
Job status examples: Job Accepted > Worker > Reviewer > Manager > Complete

I need to record the amount of time that a record spends in each status.

I’m looking for something like this:

• Formula field - Time spent with Worker: 100 minutes
• Formula field - Time spent with Reviewer: 25 minutes
• Formula field - Time spent with Manager: 15 minutes

The catch: records will sometimes move backwards and forwards through the statuses. i.e., record status will go from Worker > Reviewer > Worker > Reviewer > Manager.

I can’t figure out how to make this happen (or if it is even possible).
Can anyone think of a way to do this? I’d love to hear an alternate approach if there is one!

[TheTimeSavingCo]

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

[twoodl]

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!

[TheTimeSavingCo]

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

[TheTimeSavingCo]

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

[twoodl]

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?

[TheTimeSavingCo]

Hey twoodl, no worries!

twoodl:

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?

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

[samwusegero]

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