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!