Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

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

Topic Labels: Formulas
124 3
cancel
Showing results for 
Search instead for 
Did you mean: 

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

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:
    image

  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
    image

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

  9. Select the “Last Modified” field from the “When a record is updated” trigger:
    image

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?