Date column triggering urgency status column
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!
Best Answer
-
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")))
Answers
-
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.
Kelly Drake (she/her/hers)
STARBUCKS COFFEE COMPANY| business optimization product manager
-
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..
-
Hi @Paul Newcome do you have any ideas on why I might be getting an unparseable error on the above formula? Thank you!
-
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.
-
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?
-
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")))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!