Highlight a date when a date is past due

Tania Nassar

Hello,

I would like the "Bid Back" date to highlight in red if it is past the date in red in the Trade column. I would also like the "Bid Back" date to highlight in Yellow if it is within 3 days of the red date in the trade column. And green if the date is on or before the date in red. Is this possible?

Please help!

Mike Wilday

You should be able to complete all of these need with conditional formatting.

https://help.smartsheet.com/videos/conditional-formatting

https://help.smartsheet.com/learning-track/get-started/conditional-formatting

You can create multiple conditional formatting rules by clicking on the down arrow next to the rule once you set that up.

2020-01-10_08-54-11 (1).gif

Tania Nassar

I don't think that will work because It is to a specific date in red??

Paul Newcome

If your status column (the RYG one) is set to reflect the same, you can drive your conditional formatting from that. Otherwise you will need an additional column (that can be hidden after setting it up) that will check a box or flag or whatever other output you want on a row by row basis. Then you would set your conditional formatting to be based off of this new column.

Mike Wilday

I see. You could create a helper column with the following formula...

=IF([Bid Back]@row > Trade2, "Red", IF(AND([Bid Back]@row >= Trade2 - 3, [Bid Back]@row < Trade2), "Yellow", ""))

Then you could apply conditional formatting on the bid-back cell if the helper column in that row says Yellow or Red. See my example below.

2020-01-10_09-42-02 (1).gif

Tania Nassar

I get #invalid operation

actually...I may have messed up my explanation of what I want. I want the bid back cell to be red if the cell is blank and past the trade date in red. sorry about that

Mike Wilday

Okay, this one should account for if its blank OR if its past the due date. You probably got invalid operation because the cell was blank. This will account for that.

=IF(OR([Bid Back]@row > Trade2, ISBLANK([Bid Back]@row)), "Red", IF(AND([Bid Back]@row >= Trade2 - 3, [Bid Back]@row < Trade2), "Yellow", ""))