Help

Status based on expiry date

Topic Labels: Formulas
Solved
Jump to Solution
217 2
cancel
Showing results for 
Search instead for 
Did you mean: 
joshmead
4 - Data Explorer
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
Dan_Montoya
Community Manager
Community Manager

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.

See Solution in Thread

2 Replies 2
Dan_Montoya
Community Manager
Community Manager

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