Hello, Novice needing formula help.

C. Phillips

I have searched help topics but still find myself challenged. I would like the Date due cell to turn red if the Date Due has passed. Also, would like the Date Due cell to turn green if the Check In Date was before the Date Due. I basically need a visual reminder if the Date Due was missed and needs attention, but do not want it to remain red if there is a date entered into the Check In Date cell. Please excuse my ignorance with this.

Thank you kindly in advance for any help you are willing to provide.

Andrée Starå

Hi @C. Phillips

I hope you're well and safe!

To add to Tariq's excellent advice/answer.

For the second rule, you must add a so-called helper column with a formula that checks if the check-in date was before the date due.

Try something like this. (in a checkbox column that you reference in the rule, and you can convert it to a column formula)

=IF([Check In Date]@row <> "", IF([Check In Date]@row < [Date Due]@row, 1))

Did that work/help?

I hope that helps!

Have a fantastic weekend & Happy Holidays!

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, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

tariq.shaikh@signspecialists.com

Hi C!

You may like to try creating 2 conditional formatting rules for Due Date column:

If Due Date is in the past and Check In Date is blank then apply this format to the Due Date Column

If Check in date is a date then apply this format to the Due Date Column

I have attached a screen shot and hope that helps.

You can add the "And" condition by clicking on the dropdown symbol on the rule and play with the conditions for fine tuning or adding more logic.

Conditionl Formatting.png

Best of luck!

Tariq Shaikh

Andrée Starå

Hi @C. Phillips

I hope you're well and safe!

To add to Tariq's excellent advice/answer.

For the second rule, you must add a so-called helper column with a formula that checks if the check-in date was before the date due.

Try something like this. (in a checkbox column that you reference in the rule, and you can convert it to a column formula)

=IF([Check In Date]@row <> "", IF([Check In Date]@row < [Date Due]@row, 1))

Did that work/help?

I hope that helps!

Have a fantastic weekend & Happy Holidays!

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, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

C. Phillips

Tariq & Andrée,

Thank you both very kindly for taking the time to help with my issue. Everything worked perfectly!

Thanks again and have a great day!

C. Phillips

Andrée Starå

@C. Phillips

Excellent!

You're more than welcome!

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