Skip to main content
Solved

Conditional "IF" formula--directing Airtable to check a second cell if the first is blank

  • May 10, 2023
  • 3 replies
  • 34 views
E
Forum|alt.badge.img+1

I'm creating an inventory checkout database, based on a sandbox version created by another community member, and am trying to figure out a formula to indicate if items are overdue. Our default check-out time is one business day, and I've managed to create a formula that auto-populates the default due date, and a formula (below) that will indicate if an item is overdue, past that default date

IF(
AND(
{Type}="Checking Out",
IS_AFTER(
TODAY(),{Due Date})
),
'OVERDUE')
 
BUT the issue I'm encountering is that sometimes, we extend checkouts in special circumstances. I've created an "Extended Due Date" field, so that staff can manually create an extended due date if needed. However, I'd now like to figure out a formula for this Overdue field that would tell Airtable to first check the Extended Due Date field, and use that if it's populated, and then, if that field is empty, check the Due Date field. How would I do this? Thanks in advance!

Best answer by MelloJello

IF({Extended Date},IF(AND({Type}="Checking Out",IS_AFTER(TODAY(),{Extended Date})),'OVERDUE'),IF(AND({Type}="Checking Out",
IS_AFTER(TODAY(),{Due Date})),'OVERDUE'))

3 replies

M
Forum|alt.badge.img+2
  • MelloJello
  • Participating Frequently
  • Answer
  • May 10, 2023
IF({Extended Date},IF(AND({Type}="Checking Out",IS_AFTER(TODAY(),{Extended Date})),'OVERDUE'),IF(AND({Type}="Checking Out",
IS_AFTER(TODAY(),{Due Date})),'OVERDUE'))
E
Forum|alt.badge.img+1
  • Eliz_Comm
  • Author
  • New Participant
  • May 10, 2023
IF({Extended Date},IF(AND({Type}="Checking Out",IS_AFTER(TODAY(),{Extended Date})),'OVERDUE'),IF(AND({Type}="Checking Out",
IS_AFTER(TODAY(),{Due Date})),'OVERDUE'))

This solved the problem!! THANK YOU!!  😀

M
Forum|alt.badge.img+2
  • MelloJello
  • Participating Frequently
  • May 10, 2023

Oh Good! Here is how I came to the solution:

First I add the check to see if there is anything in Extended Date. We do not need to specify what is in the cell, only that something is there.

IF({Extended Date},

 

Then I copied your existing formula that looks at the Due Date and changed it to look for the Extension Date.

IF(AND({Type}="Checking Out",IS_AFTER(TODAY(),{Extended Date})),'OVERDUE'),

Then to wrap it all up I added your original formula as is

IF(AND({Type}="Checking Out",IS_AFTER(TODAY(),{Due Date})),'OVERDUE'))

 

Each IF statement follows the same logic of:

IF(something is true, do this, otherwise do this)

Terms & ConditionsAccessibility statement