Skip to main content
Solved

Status based on expiry date

  • April 5, 2024
  • 2 replies
  • 38 views
J
Forum|alt.badge.img+3

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?

Best answer by Dan_Montoya

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.

2 replies

Dan_Montoya
Forum|alt.badge.img+17
  • Dan_MontoyaEmployee
  • Employee
  • Answer
  • April 5, 2024

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.

J
J
Forum|alt.badge.img+3
  • joshmead
  • Author
  • New Participant
  • April 5, 2024

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"))
Terms & ConditionsAccessibility statement