How do I set up the RAG status to automatically populate based on target end date vs actual end date
I'm trying to get the red, yellow or green status to automatically populate based on the difference in days for target end date and actual end date?
If the actual end date is 10 days or more past the target end date, it should be red.
If the actual end date is more than 1 day but less than 10 days past the target end date, it should be yellow.
If the actual end date is the same or earlier than the target end date, it should be green.
Please help.
Answers
-
You will need to adjust for actual column names, but this should work.
=IF(AND(ISDATE([Target End Date]@row), ISDATE([Actual End Date]@row)), IF([Actual End Date]@row >= [Target End Date]@row + 10, "Red", IF([Actual End Date]@row > [Target End Date]@row, "Yellow", "Green")))
-
Hi @johnna.young
Here are a couple of assumptions
You want to count calendar days and not working days
I can change the second clause to make sure you have a color for if the actual end date was exactly 1 day after the target:
- If the actual end date is
more than1 day or more but less than 10 days past the target end date, it should be yellow.
Here is the formula (you might need to change the column headings)
=IF([Actual end date]@row - [Target end date]@row >= 10, "Red", IF([Actual end date]@row - [Target end date]@row <= 0, "Green", "Yellow"))
Here is how it looks with some sample data
Here is how I made it
Start by calculating the difference between the dates using
=[Actual end date]@row - [Target end date]@row
Then add an IF around that calculation to set up your "Red"
=IF([Actual end date]@row - [Target end date]@row >= 10, "Red")
(in other words, if the actual end date minus the target end date is 10 or more put a red icon)
Then add an another IF to set up your "Green" and put this in the position to be evaluated if the first IF is not true
=IF([Actual end date]@row - [Target end date]@row >= 10, "Red", IF([Actual end date]@row - [Target end date]@row <= 0, "Green"))
(in other words, if the actual end date minus the target end date is 10 or more put a red icon, if not but if 0 or less put a green icon)
You could then make everything else amber using this:
=IF([Actual end date]@row - [Target end date]@row >= 10, "Red", IF([Actual end date]@row -One [Target end date]@row <= 0, "Green", "Yellow"))
(in other words, if the actual end date minus the target end date is 10 or more put a red icon, if not but if 0 or less put a green icon, and if not 0 or less put a yellow icon)
Bonus points
One additional thing you may want to do is wrap the whole formula in another formula so the icons only appear if both Target end date and Actual end date are populated.
This will put nothing in the column if either of the dates are blank:
=IF(ISBLANK([Actual end date]@row - [Target end date]@row), "", IF([Actual end date]@row - [Target end date]@row >= 10, "Red", IF([Actual end date]@row - [Target end date]@row <= 0, "Green", "Yellow")))
This is similar but will put a gray icon:
=IF(ISBLANK([Actual end date]@row - [Target end date]@row), "Gray", IF([Actual end date]@row - [Target end date]@row >= 10, "Red", IF([Actual end date]@row - [Target end date]@row <= 0, "Green", "Yellow")))
- If the actual end date is
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 352 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 135 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!