Updating status with formula field

Ask the community Formulas
#1

Hello, I am horrible at formulas and it’s really becoming a frustration as I continue to learn Airtable. I am wanting to create a status in the formula field to then automate an email trigger.

  1. The formula needs to update to either 60-days or 30-days based on the contract end date.
  2. I already have fields for the start date and end date so the formula can work with either.

Seems simple enough but I am a struggle bus.

What formula do I need to enter? If we are 60-days out from the contract ending, update the status to 60-days, and if we a 30-days from the contract ending, update the status to 30-days.

Start with IF(AND(, correct?

IF(AND({Contract End Date} …blank stare…, “60-days”), repeat blank stare, “30-days”)

Help :weary:

#2

See the formula below which should do as you asked using 4 nested IFs

IF(
   {Contract End Date}, 
   IF(
      {Contract End Date} > TODAY(), 
      IF(
         DATETIME_DIFF({Contract End Date}, TODAY(), "days") <= 30, 
         "30 days", 
         IF(
            DATETIME_DIFF({Contract End Date}, TODAY(), "days") <= 60, 
            "60 days",
            "more than 60 days"
         )
      ),
      "contract end date passed"
   ),
   "no contract end date"
)

If you don’t want any text to display if its neither within 30 days nor 60 days then simplify that formula to:

IF(
   {Contract End Date}, 
   IF(
      {Contract End Date} > TODAY(), 
      IF(
         DATETIME_DIFF({Contract End Date}, TODAY(), "days") <= 30, 
         "30 days", 
         IF(
            DATETIME_DIFF({Contract End Date}, TODAY(), "days") <= 60, 
            "60 days"
         )
      )
   )
)
3 Likes
#3

You just saved me! This makes zero sense right now, but it will over the next few months as I keep using Airtable. Thank you so much! I am beyond appreciative of your help!! :raised_hands:t5:

#4

Working from outside in:

  1. The first IF() makes sure the End Date field isn’t empty. This prevents the field from showing errors
  2. The second IF() makes sure that the End Date hasn’t happened yet. If its already occurred then you will get a negative number for “the difference between days”
  3. The third and 4th IF()s are straightforward. They are presented in this order because checking for if a number is less than 60 first may include numbers that are also less than 30. “A number less than 30 must be less than 60”, so logically we want to know “If it is not less than 30, is it less than 60?”
1 Like
#5

This topic was solved and automatically closed 3 days after the last reply. New replies are no longer allowed.