Combining two IF formulas to output When Due OR Complete

[britterz05]

Hello Airtable Community,

I'm new to writing code and am struggling to combine two different formulas into one. I have 4 columns I'm working with - Due Date, Days Until Due, Date Posted, and Priority. I want the Priority Column to output "Due Today", "Due This Week", "Due Next Week", "Upcoming", "Overdue" or "Complete" based on the number of Days Until Due and the Date Posted columns. 

I have a formula working that will output "Due Today", "Due This Week", "Due Next Week", "Upcoming", and "Overdue":

IF(

{Days Until Due} < 0,

'Overdue',

IF(

{Days Until Due} = 0,

'Due Today',

IF(

{Days Until Due} <= 7,

'Due This Week',

IF(

{Days Until Due} <= 14,

'Due Next Week',

'Upcoming'

))))

and another that produces "Complete" if the Date Posted field contains data, but I can't seem to combine them in the right way.

IF(

(AND({Days Until Due} < 0, NOT({Date Posted} = BLANK()))),

'Complete')

[AirOps · Accepted solution]

Hi @britterz05 , 

Welcome to the Airtable community! 

You'll want to check for the complete case as the first part of your nested if statement, as you would only want to show the priority if the task was not complete. I also suggest you check this condition irregardless of the the "Days Until Due" formula/value. As I understand it the "Date Posted Field" is the condition you check to know if this is complete. If you check for the condition of "Days until Due" <0 you could run into the case of something being set due for August 3rd, but was posted today (August 1) showing "Due This Week" instead of "Complete". Based on this you'll want your formula to be as follows: 

IF({Date Posted},

'Complete'

IF(

{Days Until Due} < 0,

'Overdue',

IF(

{Days Until Due} = 0,

'Due Today',

IF(

{Days Until Due} <= 7,

'Due This Week',

IF(

{Days Until Due} <= 14,

'Due Next Week',

'Upcoming'

)))))

In Airtable using the IF({FIELD NAME}) returns FALSE if the field is empty and TRUE if the field contains Data, this is the simplest way of checking this condition. 

I hope this helps.

Chantal 

[britterz05]

Yes, that makes sense. Thank you so much!