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.
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.
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”.
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')
)
)
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.
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().
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().
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
