Status Symbols from Multiple Date Columns

dowelle

I'm looking to have a health status (green, yellow, red) populate by checking multiple columns with differing expiration dates.

I'm trying to come up with a formula that will return the following status':

IF[Orientation Date] is in the past and [First Aid Ex Date], [OTS Exp Date], [BBP Exp Date] are at all least 30 days in the future, than "Green"

IF[Orientation Date] is in the past and any [First Aid Ex Date], [OTS Ex Date], [BBP Ex Date] are less than 90 days in future, than "Yellow"

IF any [First Aid Ex Date], [OTS Ex Date], [BBP Ex Date] are missing/blank or in the past, than "Red"

Here is a screenshot of the column names/order.

Any advice is appreciated!

Ryan Sides

Hi @dowelle For this, you'll want to use combinations of IF statements and AND and OR functions. The AND function means that all things must be true. The OR function means that any of the things can be true.

Give this one a try...

=IF(OR(ISBLANK([Orientation Date]@row), ISBLANK([OTS Ex Date]@row), ISBLANK([BBP Ex Date]@row), [OTS Ex Date]@row < TODAY(), [BBP Ex Date]@row < TODAY()), "Red", IF([Orientation Date]@row < TODAY(), IF(AND([First Aid Ex Date]@row < TODAY(30), [OTS Ex Date]@row < TODAY(30), [BBP Ex Date]@row < TODAY(30)), "Green", IF(OR([First Aid Ex Date]@row < TODAY(90), [OTS Ex Date]@row < TODAY(90), [BBP Ex Date]@row < TODAY(90)), "Yellow", "")), ""))

Please let me know if that works out for you.