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

Topic Labels: Formulas
925 3
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

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?

3 Replies 3
8 - Airtable Astronomer
8 - Airtable Astronomer

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!

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:
Screen Shot 2022-10-19 at 4.51.21 PM

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