Date column triggering urgency status column

Shannon Scarbrough

Hello! I have three columns: 1. Date 2. Checkbox 3. Urgency status

If the date populated in the Date column is one week or sooner from today's date, and the checkbox is unchecked, I would like the urgency status to be yellow. If the date populated in the Date column is past today's date and the checkbox is unchecked, I would like the urgency status to be red. If the date in the Date column is more than 1 week away from today's date, regardless of checkbox, I would like the urgency status to be green. If the checkbox is checked, then the urgency status should be green, no matter what the date is.

Thank you!

Paul Newcome

Sure. Give this a whirl...

=IF([Date of Expected Maintenance Event]@row <> "", IF(OR([Did the maintenance event occur?]@row = 1, Date@row > TODAY(7)), "Green", IF(Date@row < TODAY(), "Red", "Yellow")))

Kelly Drake

Try this....

=IF(Checkbox@row = 1, "Green", IF(AND(Date@row < TODAY(), Checkbox@row = 0), "Red", IF(AND(Date@row <= TODAY(+7), Checkbox@row = 0), "Yellow", IF(Date@row > TODAY(+7), "Green"))))

It reorders your statements just a little bit to the following:

• If the checkbox is checked, then the urgency status should be green, no matter what the date is.
• If the date populated in the Date column is past today's date and the checkbox is unchecked, I would like the urgency status to be red. 
• If the date populated in the Date column is one week or sooner from today's date, and the checkbox is unchecked, I would like the urgency status to be yellow. 
• If the date in the Date column is more than 1 week away from today's date, regardless of checkbox, I would like the urgency status to be green. 

Shannon Scarbrough

Hi @Kelly Drake and thanks!

I typed in like this: =IF([Did the maintenance event occur?]@row = 1, "Green", IF(AND(Date@row < TODAY(), [Did the maintenance event occur?]@row = 0), "Red", IF(AND(Date@row <= TODAY(+7), [Did the maintenance event occur?]@row = 0), "Yellow", IF(Date@row > TODAY(+7), "Green"))))

and am getting unparseable..

Shannon Scarbrough

Hi @Paul Newcome do you have any ideas on why I might be getting an unparseable error on the above formula? Thank you!

Paul Newcome

Commas and parenthesis seem to all be in order, so the only thing I can think of right off would be column names.

I would actually suggest a slight rewrite for a little bit of added efficiency...

=IF(OR([Did the maintenance event occur?]@row = 1, Date@row > TODAY(7)), "Green", IF(Date@row < TODAY(), "Red", "Yellow"))

The way this works is we go ahead and get both of the "Green" outputs out of the way since those both override any other color.

Since we specified the checkbox being checked in the first IF, by default it must be unchecked to get past that which means we don't need to specify that for any other IF using an AND.

Next, since we already specified one end of the date range in the "Green" output, we specify the other end in the "Red" output, and that allows us to just use the "value if false" portion for the "Yellow" output since any dates that make it past the "Red" and "Green" must be greater than today but less than today + 7.

Shannon Scarbrough

Hi @Paul Newcome that worked! Thanks! Do you know if there's a way the Red, Yellow, Green symbol column can show up as blank if there is no date entered into the "Date of Expected Maintenance Event" column?

Paul Newcome

Sure. Give this a whirl...

=IF([Date of Expected Maintenance Event]@row <> "", IF(OR([Did the maintenance event occur?]@row = 1, Date@row > TODAY(7)), "Green", IF(Date@row < TODAY(), "Red", "Yellow")))