Skip to main content

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!

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 ✔ ”),“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.

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


You’re welcome! Glad I could help! 🙂

Reply


Terms & Conditions