Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Feb 01, 2018 08:03 AM
Hello!
I am relatively new to Airtable and am trying to figure out why my nested IF statement isn’t working.
Goal: If the due date field value is before today and the field complete is not checked show “in progress”, if complete is checked then show todays date if none of the above are true show “missed”.
Current Formula:
IF({Due Date} IS_BEFORE({Due Date}, TODAY()),“In progress”, IF(Complete=1 ,DATETIME_FORMAT(TODAY(),“l”),“Missed”))
I may be using the wrong formula as well. I tried a few variations and couldn’t get it to work. Any help would be great! Thanks!
This issue seems similar to this but not quite the same: Problem in my Nested IF-THEN-ELSE statement
Feb 01, 2018 08:35 AM
2 ideas:
IS_BEFORE()
isn’t needed, it is already in the parameters.AND()
to evaluate 2 conditions.Feb 02, 2018 05:31 AM
Hey Elias,
Thanks for the info! I have two questions about those ideas.
I tried using this formula to return text strings that I could then use in tandem with a “Complete Yes/No” field but am still receiving an error. Again, could be dumb formatting on my part I’m not sure.
IF({Due Date} = TODAY(), "Due Today"), IF({Due Date} IS_BEFORE(TODAY(),"In progress"), "Missed")
Thanks!
Feb 02, 2018 06:23 AM
About the is_before
The format is IS_BEFORE([date1], [date2])
.
Determines if [date1] is earlier than [date2]. Returns 1 if yes, 0 if no.
IS_BEFORE({12th January},TODAY())
.You don’t need to write the evaluated date before the command, and that returns TRUE
. Of course the date format is not valid, it’s just an example.
So if you want to test that in an IF, it goes:
IF(IS_BEFORE({1 January},TODAY()),"It's the past",'Today or the future')
.
Options
I have this options:
What happens if is not before, and is completed?
The formula
I don’t understand your statues, but here it is, as working in my demo table:
IF(
IS_BEFORE(
Date,
TODAY()
),
IF(
Complete,
DATETIME_FORMAT(
TODAY(),
'll'
),
'In Progress'
),
IF(
Complete,
'????',
'Complete'
)
)
Oh, I think you don’t need the AND()
function, but it works like this: AND(logical1, [logical2, ...])
.
Anyway, you can change the returning string to whatever you want, but that is the structure for 2 options and its 4 possible variations.
Feb 02, 2018 07:22 AM
This is super helpful. I was able to get what I needed with two separate formulas shown below:
IF({Due Date} >= TODAY(), "In progress", "Missed")
IF(Complete = 1, DATETIME_FORMAT(TODAY(),"l"), {Completed Date (Dependent Field)})
I think your solution is an elegant way to consolidate what I have achieved above into a single formula. Thanks again!
Feb 02, 2018 12:19 PM
I hope this helps