Creating Color Coded Column

Beronica Muller

Help me out here guys... I am trying to create a color coded formula that would produce the following results: If date file received is not blank – populate green.

If date file received column is blank AND…..

If today’s date is 1 day older than expected date column – populate yellow

If today’s date is 2+ days older than expected date column – populate red.

I took a stab at a formula and received an error message:

=IF([Date File Recd]@row, "Green", IF(AND([Date File Recd]@row = "", [Date File Expected]@row > TODAY(), "Yellow", IF(AND([Date File Recd]@row = "", [Date File Expected]@row > (2), "Red"))

Cody Holmes

Without knowing the details, your formula should look something like this:

=IF(NOT(ISBLANK([Date Received]@row)), "Green", IF(AND(ISBLANK([Date Received]@row), TODAY() - [Expected Date]@row >= 2), "Red", IF(AND(ISBLANK([Date Received]@row), TODAY() - [Expected Date]@row >= 1), "Yellow")))

Andrée Starå

Hi @Beronica Muller

I hope you're well and safe!

Try something like this.

=IF(NOT(ISBLANK([Date File Recd]@row)), "Green", IF(AND(ISBLANK([Date File Recd]@row), TODAY() - [Date File Expected]@row >= 2), "Red", IF(AND(ISBLANK([Date File Recd]@row), TODAY() - [Date File Expected]@row >= 1), "Yellow", IF([File Canceled]@row = 1, "Purple"))))

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Cody Holmes

Are you trying to do conditional formatting on a column, or are you trying to make the formulate generate the red, yellow, green symbols in a column? And what is the column name that you are trying to perform this on?

Cody Holmes

Without knowing the details, your formula should look something like this:

=IF(NOT(ISBLANK([Date Received]@row)), "Green", IF(AND(ISBLANK([Date Received]@row), TODAY() - [Expected Date]@row >= 2), "Red", IF(AND(ISBLANK([Date Received]@row), TODAY() - [Expected Date]@row >= 1), "Yellow")))

Beronica Muller

@Cody Holmes I was trying to formulate generate the red, yellow and green symbols in a column. This formula is exactly what I needed. Thank You!!

Beronica Muller

@Cody Holmes I now need to add another "IF" to the formula. I need to add the color purple for items that have a check box in the File Canceled column. I used this formula #UNPARSEABLE error. Could you take a look at it and tell me what did I do wrong?

=IF(NOT(ISBLANK([Date File Recd]@row)), "Green", IF(AND(ISBLANK([Date File Recd]@row), TODAY() - [Date File Expected]@row >= 2), "Red", IF(AND(ISBLANK([Date File Recd]@row), TODAY() - [Date File Expected]@row >= 1), "Yellow", IF(AND([File Canceled]@row = 1), “Purple”)))

Andrée Starå

Hi @Beronica Muller

I hope you're well and safe!

Try something like this.

=IF(NOT(ISBLANK([Date File Recd]@row)), "Green", IF(AND(ISBLANK([Date File Recd]@row), TODAY() - [Date File Expected]@row >= 2), "Red", IF(AND(ISBLANK([Date File Recd]@row), TODAY() - [Date File Expected]@row >= 1), "Yellow", IF([File Canceled]@row = 1, "Purple"))))

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Beronica Muller

@Andrée Starå Would I need to begin the formula with the last IF statement first? My reasoning for asking is because if the File Canceled column is checked, then the row would need to be purple. I tested this and it does not seem to work.