How do I change the color of a cell when future dates are reached

Question

I have an excel table that is constantly filled with new rows of info and one of the columns is related to a date (sometimes it's empty - no date is to be entered) on which a customer was contacted and if no reply was received within 10 days since then we have to send a reminder and if it passed 17 days we have to cancel the order.

I experimented with several methods. I don't want to use formulas to create new columns, therefore I wondered whether Conditional Formatting could be used instead.

As an example:

• If it's been 10 days after the written date, it can be changed to orange; if it's been 17 days, it can be changed to red; if it's over the 17 days, it can be returned to white (the last one is optional).

Is there anybody who can assist me? =) I appreciate it.

Answer 1

In this answer I'm assuming your date column is column A of the sheet. Change cell references accordingly for your formulas.

Select all your data by click-dragging on the relevant column headers (e.g. A to D). Then in the Home tab click Conditional Formatting > New Rule.
Then select 'Use a formula to determine which cells to format'

Now, enter a relevant formula that will evaluate to TRUE where you want the formatting to apply, and FALSE everywhere else. In your case;

• 10-16 days after written date: =AND(TODAY()-$A1<=16, TODAY()-$A1>=10)
• 17 days after written date: =TODAY()-$A1=17
• More than 17 days: =TODAY()-$A1 > 17

With a relevant formula in, click the 'Format' button to choose how to format cells where that formula applies - including cell colour, font, border and number format. The different colours you've suggested will need to be added as separate conditional formatting rules one for each colour.

Once happy with all that, click 'OK' to save and apply the rule.