Re: Help with formula that calculates amount of days from when a record was marked with a specific category in a field
There are two sub-problems here:
- How to save the date/time when a record was tagged with a specific label in a specific column/field
- Given #1, how to determine days since that tag was applied
Part 2 can be solved in a formula, but I think Part 1 will require an Automation.
Let’s do Part 2 first:
When you say “tagged with a specific label” I will assume this means that you have Multiple Select field and there is a particular Option in that field that you care about.
We will need to create a new column to save the date/time when the label was applied. Make a date-type column, let’s call it “Label Applied.”
Now in a formula column, you can add the formula:
IF({Label Applied},DATETIME_DIFF(NOW(),{Label Applied},'days'))
If you manually enter a date in the “Label Applied” field, this formula field will show you how many days since that date. Nifty!
Part 1: filling in the Label Applied field
-
To make this automation work, we will need to add a field of type “Last modified time.” Let’s call it “Last Modified.” This will automatically tell us the date and time that each record was last modified.
-
Create a new automation.
-
For the trigger, select “When a record is updated” and select the relevant Table and the Field you are trying to watch.
-
Now create a Conditional Action in the automation
-
Fill in the condition like this:
-
Inside the condition’s body add the “Update record” action
-
For the record ID to modify choose the “Airtable record ID” from the “When a record is updated” trigger
-
Add the “Label Applied” field to the list of fields to update and change it to a Dynamic value
-
Select the “Last Modified” field from the “When a record is updated” trigger:
That’s it. Turn on your Automation and you should be good to go.
What happens?
- Every time a record is updated, the Last Modified column will automatically update itself, and the automation will be triggered.
- If the automation sees that the option you care about has been applied to the column you care about AND it sees that there is not yet a “Label Applied” date on the record, it will save the Last Modified date as the “Label Applied” date
- The formula from Part 2 will now calculate how many days between the current day and when the label was applied.
Hope that helps!
Hi, Nathaniel!
Your formula was exactly what I needed! Thank you for that. I’m wondering if you know how to incorporate Workday_Diff into it so it only counts workdays in the total, not weekends. I’ve been struggling to make your formula work with Workday_Diff, but I’m also not very good with formulas. :grimacing:
Here is a screenshot of the Airtable formula I want to make work with yours:
