Skip to main content

Question:

How can I capture how long a record has spent in a Single Select field option throughout its lifecycle?


Field Information:



  • Field Type: Single Select

  • Field Name: Status

  • Field Option: “Awaiting Information”


Context:



  1. Record enters table (Status automatically changes to “In Progress” through automation).

  2. User changes Status to “Awaiting Information” when appropriate.

  3. When user is finished, they change the Status to “Complete”.


Goal:

I want to see how long we are waiting on other people for (on average) when fulfilling these requests.


I wasn’t sure if this was a formula or automation question. Thanks!

Will the status change to “Awaiting Information” more than once in the life cycle of the record, or does that only happen one time before the record is marked “Complete”?

Will the status change to “Awaiting Information” more than once in the life cycle of the record, or does that only happen one time before the record is marked “Complete”?


Hi Justin, yes it can change to this status more than once

Hi Justin, yes it can change to this status more than once


Thanks for the update. That complicates things a bit, but I’ve actually already solved for a situation like this. A while ago I created an automation that allows for more robust activity timing than Airtable’s “Record Timer” app. In short, it’s driven by a single-select field. Set it to “Start” and an automation saves a timestamp in the background (in a field that can remain hidden). Clear the single-select field and another timestamp is stored, and the difference between the two is calculated and output to a duration field. This can be repeated as many times as necessary, and the differences between the various timestamp pairs are all added up to give you a total of how long the record was in “Start” mode. This setup could be modified for your use case fairly easily. If this sounds like it’ll work, contact me directly and we can talk about the details.

Thanks for the update. That complicates things a bit, but I’ve actually already solved for a situation like this. A while ago I created an automation that allows for more robust activity timing than Airtable’s “Record Timer” app. In short, it’s driven by a single-select field. Set it to “Start” and an automation saves a timestamp in the background (in a field that can remain hidden). Clear the single-select field and another timestamp is stored, and the difference between the two is calculated and output to a duration field. This can be repeated as many times as necessary, and the differences between the various timestamp pairs are all added up to give you a total of how long the record was in “Start” mode. This setup could be modified for your use case fairly easily. If this sounds like it’ll work, contact me directly and we can talk about the details.


Thanks Justin - I was able to create something based on your input and I think it’s working! Thanks again for your help

Reply


Terms & Conditions