Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Status based on expiry date

Topic Labels: Formulas
Solved
Jump to Solution
827 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"))