Display RYGB Circles based on dates in multiple columns
Need help trying to display the RYGB circles based on values in other columns.
So, say we have columns that are
ActualStartDateStep1, BaselineFinishDate, CalculatedFinishDate (meaning calculates on the fly as other dates are filled out, re-forecasting the possible finish date), and ActualEndDateStep7. (I have multiple steps with start and end dates across in columns).
....EXPECTED RESULTS: ....
.....If ActualStartDateStep1 is BLANK, symbol = blue (meaning, the line item project has not started yet)
OR
..........If ActualStartDateStep1 is NOT BLANK, then (meaning, the line item project has started)
..........If ActualEndDateStep7 is BLANK, then (meaning, the line item project is in progress)
...............If CalculatedFinishDate = BaselineFinishDate, symbol = green
...............If CalculatedFinishDate < BaselineFinishDate, symbol = green
...............If CalculatedFinishDate is 1 to 3 days after BaselineFinishDate, symbol = yellow
............... If CalculatedFinishDate is 4 or more days after BaselineFinishDate, symbol = red
OR
..........If ActualEndDateStep7 is NOT BLANK then (meaning, the line item project has completed)
...............ActualEndDateStep7 < or = BaselineFinishDate, symbol = green
............... ActualEndDateStep7 within 1 to 3 days of BaselineFinishDate, symbol = yellow
...............ActualEndDateStep7 is 4 or more days after BaselineFinishDate, symbol = red
RYGB column properties is set as Symbol:
Thank you in advance.
Best Answer
-
I will rename the columns and check if it works.
Answers
-
This should give you some insight into how the formula would be created. I've done 90% of it for you. I'm not sure I understand what you mean by your last part.
Example sheet:
Symbols formula:
=IF(ISBLANK([ActualStartDateStep1]@row), "Blue", IF(AND(ISBLANK([ActualEndDateStep7]@row), NOT(ISBLANK([ActualStartDateStep1]@row)), CalculatedFinishDate@row = BaselineFinishDate@row), "Green", IF(AND(ISBLANK([ActualEndDateStep7]@row), NOT(ISBLANK([ActualStartDateStep1]@row)), CalculatedFinishDate@row < BaselineFinishDate@row), "Green", IF(AND(ISBLANK([ActualEndDateStep7]@row), NOT(ISBLANK([ActualStartDateStep1]@row)), CalculatedFinishDate@row = BaselineFinishDate@row + 1), "Yellow", IF(AND(ISBLANK([ActualEndDateStep7]@row), NOT(ISBLANK([ActualStartDateStep1]@row)), CalculatedFinishDate@row = BaselineFinishDate@row + 2), "Yellow", IF(AND(ISBLANK([ActualEndDateStep7]@row), NOT(ISBLANK([ActualStartDateStep1]@row)), CalculatedFinishDate@row = BaselineFinishDate@row + 3), "Yellow", IF(AND(ISBLANK([ActualEndDateStep7]@row), NOT(ISBLANK([ActualStartDateStep1]@row)), CalculatedFinishDate@row > BaselineFinishDate@row + 3), "Red", "")))))))
-
Maybe this is what you meant by the last part.
Example sheet:
Symbols formula:
=IF(ISBLANK([ActualStartDateStep1]@row), "Blue", IF(AND(ISBLANK([ActualEndDateStep7]@row), NOT(ISBLANK([ActualStartDateStep1]@row)), CalculatedFinishDate@row = BaselineFinishDate@row), "Green", IF(AND(ISBLANK([ActualEndDateStep7]@row), NOT(ISBLANK([ActualStartDateStep1]@row)), CalculatedFinishDate@row < BaselineFinishDate@row), "Green", IF(AND(ISBLANK([ActualEndDateStep7]@row), NOT(ISBLANK([ActualStartDateStep1]@row)), CalculatedFinishDate@row = BaselineFinishDate@row + 1), "Yellow", IF(AND(ISBLANK([ActualEndDateStep7]@row), NOT(ISBLANK([ActualStartDateStep1]@row)), CalculatedFinishDate@row = BaselineFinishDate@row + 2), "Yellow", IF(AND(ISBLANK([ActualEndDateStep7]@row), NOT(ISBLANK([ActualStartDateStep1]@row)), CalculatedFinishDate@row = BaselineFinishDate@row + 3), "Yellow", IF(AND(ISBLANK([ActualEndDateStep7]@row), NOT(ISBLANK([ActualStartDateStep1]@row)), CalculatedFinishDate@row > BaselineFinishDate@row + 3), "Red", IF(AND(NOT(ISBLANK([ActualEndDateStep7]@row)), [ActualEndDateStep7]@row <= BaselineFinishDate@row), "Green", IF(AND(NOT(ISBLANK([ActualEndDateStep7]@row)), [ActualEndDateStep7]@row = BaselineFinishDate@row + 1), "Yellow", IF(AND(NOT(ISBLANK([ActualEndDateStep7]@row)), [ActualEndDateStep7]@row = BaselineFinishDate@row + 2), "Yellow", IF(AND(NOT(ISBLANK([ActualEndDateStep7]@row)), [ActualEndDateStep7]@row = BaselineFinishDate@row + 3), "Yellow", IF(AND(NOT(ISBLANK([ActualEndDateStep7]@row)), [ActualEndDateStep7]@row > BaselineFinishDate@row + 3), "Red", ""))))))))))))
-
Says #UNPARSEABLE. All I changed out were field names. Items in bold are underlined in red when reviewing the formula.
=IF(ISBLANK([STEP 1A - GATHER REQUIREMENTS START DATE *ACTUAL*]@row), "Blue", IF(AND(ISBLANK([STEP 7B - TEAM LEAD SIGNOFF [DATE] *ACTUAL*]@row), NOT(ISBLANK([STEP 1A - GATHER REQUIREMENTS START DATE *ACTUAL*]@row)), CalculatedFinishDate@row = BaselineFinishDate@row), "Green", IF(AND(ISBLANK([STEP 7B - TEAM LEAD SIGNOFF [DATE] *ACTUAL*]@row), NOT(ISBLANK([STEP 1A - GATHER REQUIREMENTS START DATE *ACTUAL*]@row)), CalculatedFinishDate@row < BaselineFinishDate@row), "Green", ...... (etc)
-
It worked for me as you could see. Make sure your columns with dates are actually date columns. Double click them and make sure Date is the type of column it is.
-
Did that work for you?
-
All date columns are confirmed as Date. Symbol column is type Symbol.
Would it be easier to create a column that counted the number of days difference between the two sets of fields and then just refer to a hidden column containing the value?
Thank you.
-
If you only have those 2 column names that are different than your original explanation, then this is your formula. BTW, you shouldn't use [ and ] in the column names because it makes it more difficult to write the formula. See how it had to put \ in front of the [ and ] in the formula.
=IF(ISBLANK([Step 1A - GATHER REQUIREMENTS START DATE *ACTUAL*]@row), "Blue", IF(AND(ISBLANK([STEP 7B - TEAM LEAD SIGNOFF \[DATE\] *ACTUAL*]@row), NOT(ISBLANK([Step 1A - GATHER REQUIREMENTS START DATE *ACTUAL*]@row)), CalculatedFinishDate@row = BaselineFinishDate@row), "Green", IF(AND(ISBLANK([STEP 7B - TEAM LEAD SIGNOFF \[DATE\] *ACTUAL*]@row), NOT(ISBLANK([Step 1A - GATHER REQUIREMENTS START DATE *ACTUAL*]@row)), CalculatedFinishDate@row < BaselineFinishDate@row), "Green", IF(AND(ISBLANK([STEP 7B - TEAM LEAD SIGNOFF \[DATE\] *ACTUAL*]@row), NOT(ISBLANK([Step 1A - GATHER REQUIREMENTS START DATE *ACTUAL*]@row)), CalculatedFinishDate@row = BaselineFinishDate@row + 1), "Yellow", IF(AND(ISBLANK([STEP 7B - TEAM LEAD SIGNOFF \[DATE\] *ACTUAL*]@row), NOT(ISBLANK([Step 1A - GATHER REQUIREMENTS START DATE *ACTUAL*]@row)), CalculatedFinishDate@row = BaselineFinishDate@row + 2), "Yellow", IF(AND(ISBLANK([STEP 7B - TEAM LEAD SIGNOFF \[DATE\] *ACTUAL*]@row), NOT(ISBLANK([Step 1A - GATHER REQUIREMENTS START DATE *ACTUAL*]@row)), CalculatedFinishDate@row = BaselineFinishDate@row + 3), "Yellow", IF(AND(ISBLANK([STEP 7B - TEAM LEAD SIGNOFF \[DATE\] *ACTUAL*]@row), NOT(ISBLANK([Step 1A - GATHER REQUIREMENTS START DATE *ACTUAL*]@row)), CalculatedFinishDate@row > BaselineFinishDate@row + 3), "Red", IF(AND(NOT(ISBLANK([STEP 7B - TEAM LEAD SIGNOFF \[DATE\] *ACTUAL*]@row)), [STEP 7B - TEAM LEAD SIGNOFF \[DATE\] *ACTUAL*]@row <= BaselineFinishDate@row), "Green", IF(AND(NOT(ISBLANK([STEP 7B - TEAM LEAD SIGNOFF \[DATE\] *ACTUAL*]@row)), [STEP 7B - TEAM LEAD SIGNOFF \[DATE\] *ACTUAL*]@row = BaselineFinishDate@row + 1), "Yellow", IF(AND(NOT(ISBLANK([STEP 7B - TEAM LEAD SIGNOFF \[DATE\] *ACTUAL*]@row)), [STEP 7B - TEAM LEAD SIGNOFF \[DATE\] *ACTUAL*]@row = BaselineFinishDate@row + 2), "Yellow", IF(AND(NOT(ISBLANK([STEP 7B - TEAM LEAD SIGNOFF \[DATE\] *ACTUAL*]@row)), [STEP 7B - TEAM LEAD SIGNOFF \[DATE\] *ACTUAL*]@row = BaselineFinishDate@row + 3), "Yellow", IF(AND(NOT(ISBLANK([STEP 7B - TEAM LEAD SIGNOFF \[DATE\] *ACTUAL*]@row)), [STEP 7B - TEAM LEAD SIGNOFF \[DATE\] *ACTUAL*]@row > BaselineFinishDate@row + 3), "Red", ""))))))))))))
-
I will rename the columns and check if it works.
-
Thank you.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!