# Re: Status based on expiry date

Solved
244 1
cancel
Showing results for
Did you mean:
4 - Data Explorer

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?
1 Solution

Accepted Solutions
Community Manager

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)

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.

2 Replies 2
Community Manager

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)

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.

4 - Data Explorer

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"))``````