The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.
Oct 12, 2022 10:47 AM
What is the formula I input if i’m looking to create a new field that calculates days since a Record ID was tagged with a specific label in a specific column/field?
Oct 12, 2022 03:12 PM
There are two sub-problems here:
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?
Hope that helps!
Oct 19, 2022 03:52 PM
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:
Oct 19, 2022 04:31 PM
hmm… what have you tried? What problem are you running into?