Conditional Formatting based on Dates

Joe Goetschel

Is there a way to have the system highlight using Conditional Formatting when it's past a number of days?

Yellow when it's 30 days after "booked date"

Orange when it is 45 days after "booked date"

Red when it is 60 days and older after "booked date"

Paul Newcome

Try this...

=IF(TODAY() > [Booked Date]@row + 60, "Red", IF(TODAY() > [Booked Date]@row + 45, "Orange", IF(TODAY() > [Booked Date]@row + 30, "Yellow")))

Then you can set up your conditional formatting to highlight your column(s) based on the text value in this column.

Bassam Khalil

Hi @Joe Goetschel 

Hope you are fine, you can create a helper column calculatin how many day past the booked day and create your Conditional Formatting rule using this helper column:

past days = today() - booked date

then you create the Conditional Formatting rule

Joe Goetschel

HI @Bassam.M Khalil

How would the formula read? I can do a DATEONLY to get the correct dates for each level in 3 different columns.

=DATEONLY([Proposal Delivery Date]@row + 30)

=DATEONLY([Proposal Delivery Date]@row + 45)

=DATEONLY([Proposal Delivery Date]@row + 60)

Is there an IF I can do that takes all of these and have it give me 0, 30, 45, 60?

Bassam Khalil

@Joe Goetschel 

Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

Paul Newcome

Try this...

=IF(TODAY() > [Booked Date]@row + 60, "Red", IF(TODAY() > [Booked Date]@row + 45, "Orange", IF(TODAY() > [Booked Date]@row + 30, "Yellow")))

Then you can set up your conditional formatting to highlight your column(s) based on the text value in this column.

Joe Goetschel

Thank you @Paul Newcome that did the trick!

Paul Newcome

Happy to help. 👍️