# Display RYGB Circles based on dates in multiple columns

Options
✭✭
edited 05/04/22

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:

Tags:

• ✭✭
Options

I will rename the columns and check if it works.

• ✭✭✭✭✭✭
Options

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", "")))))))

• ✭✭✭✭✭✭
edited 05/04/22
Options

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", ""))))))))))))

• ✭✭
edited 05/05/22
Options

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)

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

Did that work for you?

• ✭✭
Options

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.

• ✭✭✭✭✭✭
edited 05/06/22
Options

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.

• ✭✭
Options

I will rename the columns and check if it works.

• ✭✭
Options

Thank you.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!