Help

The Community will be undergoing maintenance from Friday February 21 - Friday, February 28 and will be "read only" during this time. To learn more, check out our Announcements blog post.

Creating conditional formula that doesn't calculate if Status field says "Completed"

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

Very new to Airtable and having some fun with formulas.

I have project records with a simple status field (Planned, In Progress, Completed) and some basic date fields.

I created a formula that checks if the project is "On time" or "Overdue" based on the completed (planned or actually completed) date field vs today.

I don't need this formula to calculate on Completed projects so i'm looking for a formula that sets the field to blank if status is Completed, but calculates if Planned or In Progress.

Current checking formula:

IF({Completed Date} < NOW(), "Overdue", IF(IS_SAME({Completed Date}, TODAY(), 'day'), "At Risk", "On Time"))
1 Solution

Accepted Solutions
Mike_AutomaticN
10 - Mercury
10 - Mercury

Hey @AdamZame!

You’ll want to have a nested IF formula. Please give this a shot and let me know if it works for you:

IF(
{Status} = "Completed",
BLANK(),
IF(
{Completed Date} < NOW(),
"Overdue",
IF(
IS_SAME({Completed Date}, TODAY(), 'day'),
"At Risk",
"On Time"
)
)
)

 

Mike, Consultant @ Automatic Nation

See Solution in Thread

3 Replies 3
Mike_AutomaticN
10 - Mercury
10 - Mercury

Hey @AdamZame!

You’ll want to have a nested IF formula. Please give this a shot and let me know if it works for you:

IF(
{Status} = "Completed",
BLANK(),
IF(
{Completed Date} < NOW(),
"Overdue",
IF(
IS_SAME({Completed Date}, TODAY(), 'day'),
"At Risk",
"On Time"
)
)
)

 

Mike, Consultant @ Automatic Nation

Thanks Mike,
That worked great. 

Mike_AutomaticN
10 - Mercury
10 - Mercury

Always happy to help @AdamZame!

If you have any other Airtable related question, feel free to schedule a call using this link!

Mike, Consultant @ Automatic Nation