Adding in element to this IF AND formula

[Jeannie_Ruesch_]

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 :no_entry: ”, IF(IS_SAME({Due Date},TODAY()),"Due Today :eight_spoked_asterisk: ",“Upcoming”))))

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

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

[ScottWorld · Accepted solution]

Hi @Jeannie_Ruesch_BDC,

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 :heavy_check_mark: ”),“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 :no_entry: ”, IF(IS_SAME({Due Date},TODAY()),"Due Today :eight_spoked_asterisk: ",“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")))))

[Jeannie_Ruesch_]

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

[ScottWorld]

You’re welcome! Glad I could help! :slightly_smiling_face: