 # Adding in element to this IF AND formula

Hi there,

I was able to grab a formula from this amazing community to show status of due items, which is working great – but I need to add in one layer and my attempts so far have only produced errors. Not sure if it’s possible or what I’m doing wrong.

The current formula:

IF({Due Date}="",“Not Set”, IF(AND(DATETIME_DIFF({Due Date},TODAY(),‘hours’) >=12,DATETIME_DIFF({Due Date},TODAY(),‘hours’) <=48),“Due Soon”,IF(IS_BEFORE({Due Date},TODAY()),“Past Due ”, IF(IS_SAME({Due Date},TODAY()),"Due Today ",“Upcoming”))))

What I need to add to the above is :
If Due Date is before today AND
“Status” column = Done , then value should show as “Completed”

Appreciate any help!! This community is amazing, and I’ve learned so much from you!

You just want to add that into your formula as the very first criteria to evaluate?

If so, I believe that the formula below should work. (Note that I haven’t tested this in Airtable yet, I’m just typing this up right here in the forums.)

IF(AND(IS_BEFORE({Due Date}, TODAY()),{Status}=“Done ”),“Completed”,
IF({Due Date}="",“Not Set”, IF(AND(DATETIME_DIFF({Due Date},TODAY(),‘hours’) >=12,DATETIME_DIFF({Due Date},TODAY(),‘hours’) <=48),“Due Soon”,IF(IS_BEFORE({Due Date},TODAY()),“Past Due ”, IF(IS_SAME({Due Date},TODAY()),"Due Today ",“Upcoming”)))))

That is to show you the formula as to what it would look like with the emoticons intact. However, this forum turns all the single quotes & double quotes into curly quotes, which Airtable won’t recognize. So, if you’re going to be copying-and-pasting this formula, then you’ll need to copy-and-paste the text below — but fill in the emoticons on your own:

`IF(AND(IS_BEFORE({Due Date}, TODAY()),{Status}="Done"),"Completed", IF({Due Date}="","Not Set", IF(AND(DATETIME_DIFF({Due Date},TODAY(),'hours') >=12,DATETIME_DIFF({Due Date},TODAY(),'hours') <=48),"Due Soon",IF(IS_BEFORE({Due Date},TODAY()),"Past Due", IF(IS_SAME({Due Date},TODAY()),"Due Today","Upcoming")))))`

@ScottWorld THANK YOU! That is exactly what I needed.

You’re welcome! Glad I could help! This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.