Help

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

Topic Labels: Formulas
Solved
Jump to Solution
527 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Eliz_Comm
4 - Data Explorer
4 - Data Explorer

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!
1 Solution

Accepted Solutions
MelloJello
5 - Automation Enthusiast
5 - Automation Enthusiast
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'))

See Solution in Thread

3 Replies 3
MelloJello
5 - Automation Enthusiast
5 - Automation Enthusiast
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!! 😀

MelloJello
5 - Automation Enthusiast
5 - Automation Enthusiast

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)