Display RYGB Circles based on dates in multiple columns

R12

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.

R12

I will rename the columns and check if it works.

Mike TV

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

Mike TV

@R12

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

R12

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)

Mike TV

@R12

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.

Mike TV

@R12

Did that work for you?

R12

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.

Mike TV

@R12

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

R12

I will rename the columns and check if it works.

R12

Thank you.