Status based on expiry date

[joshmead]

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?

[Dan_Montoya · Accepted solution]

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.

[joshmead]

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

---