Skip to main content

I have a formula setup to show a status based on an 'Expiry Date'.

This works from a date column {Expiry Date} with a column that works out the difference in days called {Diff}, this uses the formula:

 
DATETIME_DIFF({Expiry Date}, NOW(), "days")
 
Then is the status column I am using:
 
IF(Diff <= 0, "❌", IF(AND(Diff > 0, Diff <= 28), "⚠️"))
 
This works perfectly, my question is how do I add an additional "✅" status for all records that are in date? What would I need to add to the status column formula?

Hi @joshmead ,


I thought it might help to dig into your IF statement a little.


IF(Condition1 is true,
do this,
else do that)

a compound if statement

IF(Condition1 is true,
do this,
(Condition1 not true, do this, else do 3)

Applied to your question:

IF(Diff <= 0,
"❌",
IF(
AND(Diff > 0, (you don't need this because you already know it from the diff not being <= in condition 1
Diff <= 28),
"⚠️"), "Check " (no other condition met)

you might want to look at the SWITCH formula which may make your field formula a bit cleaner.


Hi @joshmead ,


I thought it might help to dig into your IF statement a little.


IF(Condition1 is true,
do this,
else do that)

a compound if statement

IF(Condition1 is true,
do this,
(Condition1 not true, do this, else do 3)

Applied to your question:

IF(Diff <= 0,
"❌",
IF(
AND(Diff > 0, (you don't need this because you already know it from the diff not being <= in condition 1
Diff <= 28),
"⚠️"), "Check " (no other condition met)

you might want to look at the SWITCH formula which may make your field formula a bit cleaner.



Thank you, Dan! This worked perfectly and I've learnt something new  😀

In case anyone wants my exact formula to duplicate it is:

'Diff' column - 

DATETIME_DIFF({Expiry Date}, NOW(), "days")

'Status' column -

IF(Diff <= 0,
"❌ Expired",
IF(
AND(Diff > 0,
Diff <= 28),
"🟡 Expiring soon", "✅ Valid"))

Reply


Terms & Conditions