Is there a way to make a timer increment only when a field has a certain status?

Benjamin_Tonita · ‎May 27, 2022

I am trying to make a formula that tracks how long a record has a certain “Status”.

If the status is “Open”, the timer starts.

If the status is “Pending”, the timer stops, and stores the value that it was just before the field was switched to pending.

I also want this timer to start incrementing again if the record is moved from “Pending” back to “Open” (so, not starting from zero, but starting from the paused time.

I thought this should be fairly simple, but I’m not sure how to make it work. Thanks

Kamille_Parks · ‎May 27, 2022

I haven’t fully tested this so it may not work.

Create one Formula field called Timer and a Number field called Time Elapsed (or whatever)

Set the formula to be

IF(
  OR(Status = "Open", Status = "Pending"),
  DATETIME_DIFF(
    MAX(
      LAST_MODIFIED_TIME(Status), 
      LAST_MODIFIED_TIME({Time Elapsed})
    ), NOW(), "days") + {Time Elapsed}
)

Then create an Automation that triggers when Status = “Pending” and have an Update Record step copy the value of Timer to Time Elapsed.

Kamille_Parks · ‎May 27, 2022

That actually may not work at all. Its relying on the Last Modified time of the Status field, but if you change the field to “Pending” it will reset and won’t be calculating from the time you selected “Open” anymore.

This would be easier if you had two fields. “Is Open” and “Is Pending”.

kuovonne · ‎May 27, 2022

Welcome to the Airtable community!

It is difficult to have a live timer in a formula field because the NOW() formula does not update very often. However, a system of formula fields, editable fields, and automations might work for you.

Fields:

an editable {Duration} field for storing the duration. It is managed by the automation.
a {Last Modified Time} field that shows when the {Status} field was last modified.
an editable {Start Time} field that will be managed by the automation.
a formula field that adds the difference between the {Start Time} field and the {Last Modified Time} field to the {Duration} field.

Have an automation that watches for when the {Status} field is updated. Have conditional actions based on the value of the {Status} field.

If the status was changed to “Open”, update the {Start Time} to be the {Last Modified Time}
If the status was changed to “Pending”, update the {Duration} field with the result of the formula field, and clear the {Start Time} field.

Here’s a possible value for the formula field:

IF(
  {Start Time},
  SUM(
    {Duration},
    DATETIME_DIFF({Last Modified Time}, {Start Time}, 'seconds')
  )
)

kuovonne · ‎May 27, 2022

Lol. I haven’t tested mine either, so maybe I should put the same disclaimer. For example, I might have flip-flopped the order of dates in DATETIME_DIFF().

I also find it interesting that I assumed the timer was needed for hours or minutes, but you thought “days” was a better unit of time. The original post didn’t explicitly state the units, but I think you are probably right about the units. My solution could be adjusted to work with days by using a number field instead of a duration field, and swapping out the units in DATETIME_DIFF().

Kamille_Parks · ‎May 27, 2022

Yeah i made my assumption based on guessing the record wont flip flop between “open” and "pending"throughout the day, but i coupd be wrong. I think your seystem is more robust