Help with formula that calculates amount of days from when a record was marked with a specific category in a field

[MK_Nguyen]

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?

[Nathaniel_Grano]

There are two sub-problems here:

1. How to save the date/time when a record was tagged with a specific label in a specific column/field
2. 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

1. 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.

2. Create a new automation.

3. For the trigger, select “When a record is updated” and select the relevant Table and the Field you are trying to watch.

4. Now create a Conditional Action in the automation

5. Fill in the condition like this:
   

6. Inside the condition’s body add the “Update record” action

7. For the record ID to modify choose the “Airtable record ID” from the “When a record is updated” trigger
   

8. Add the “Label Applied” field to the list of fields to update and change it to a Dynamic value
   

9. 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!

[Cara_Haynes]

Nathaniel_Granor:

IF({Label Applied},DATETIME_DIFF(NOW(),{Label Applied},'days'))

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:

[Nathaniel_Grano]

hmm… what have you tried? What problem are you running into?