Status Formula based on days away from expiration date as well as a checkbox field

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

Here is my current formula created to change the status of the form depending on the expiration of our suppliers certification:

If(ToText([Valid Until])="","No Expiration Date",If(Today()<[Valid Until] and ToNumber(ToText(ToDays([Valid Until]-Today())))>30,"Active Certificate",
 Today()<[Valid Until] and ToNumber(ToText(ToDays([Valid Until]-Today())))<=30,"Expiring","Expired"))


If no Valid Until Date entered, change status to "No Expiration Date" --not working see below

If Valid Until Date is greater than 30 days from today, change status to "Active"--working

If Valid Until Date is less than or equal to 30 days from today, change status to "Expiring"--working

If Valid Until Date is Today or in the past, change status to "Expired"--working


Question 1: How do I change the first portion of the formula to give me the result shown above. I do not enter text in this field, only a date. If no date is entered, I would like the status to show No Expiration Date. I think I need to remove the (to text) portion but when I do I keep getting errors.

Question 2: How do I add another non-date driven part to this formula? I need to add to this formula another situation:

If Archive Checkbox is checked, change status to Archived


Appreciate your help in advance!

Photo of mkosek

mkosek

  • 876 Points 500 badge 2x thumb

Posted 3 years ago

  • 0
  • 1
Photo of Jack

Jack, Champion

  • 50 Points
I think the below should work, note I havent tested it:

If(

[Archive Checkbox]=True,"Archived",

isNull([Valid Until]),"No Expiration Date",

Today()<[Valid Until] and ToNumber(ToText(ToDays([Valid Until]-Today())))>30,"Active Certificate",

Today()<[Valid Until] and ToNumber(ToText(ToDays([Valid Until]-Today())))<=30,"Expiring","Expired"))


Tips:

  • For any status that overrides everything, make that the first condition, as quickbase goes through conditions in the order you list them. 
  • Unlike excel you dont need to use If( on every line only at the start and add the closing bracket at the end of the formula.
  • I would always recommend laying out your code as above with a condition on each line, it makes it easier for you to read/understand, especially if you are working with multiple conditions.