Colour Coding a Cell Based on Date

Question

I am using 1 column for the dates that I start and end my work (1 piece of work for each row). I will enter the date I start the work and highlight the cell yellow indicating that the job is in progress. Once a job is finished, I will retype the date of completion to replace the start date and highlight the cell green. The two highlights mentioned will be done manually.

While the cell is highlighted yellow (aka the work is in progress), if todays date exceeds 14 days from when the work was started (the date entered in the cell), I would like the cell to be highlighted in red to get my attention.

Once I complete the jobs that end up highlighted in red, I will change the date to the completion date and change the highlight colour to green manually. But I don't want the cell to turn red again two weeks are the completion date as this is not necessary.

Thanks

Answer 1

Remove the existing rules.

Select H8:H13

Create a rule with formula =$H8<>"" and red as fill colour.

Create a second rule with formula =$H8>TODAY()-14 and orange/amber as fill colour.

Select H8:I13.

Create a rule with formula =$I8<>"" and green as fill colour.

Answer 2

I'd use two columns: one for the start date and another for the end date. Not only will this make the coloring much easier (it can be fully automatic), but it'll give you a better idea of how long jobs take.

In the screenshot below, I have created three conditional formatting rules.

The rules are all of type 'Use a formula to determine which cells to format'.

The rule created first (listed at the bottom in the screenshot below) uses formula =$B2<>"" and red as fill color.

The rule created second (listed in the middle) uses formula =$B2>=TODAY()-14 and yellow as fill color.

The rule created last (listed at the top) uses formula =$C2<>"" and green as fill color.

Answer 3

Hi HansV,

Thank you for your answer. Unfortunately, this didn't achieve exactly what I wanted. Please see screenshot below of the formulas entered the same as yours and the results.

I am hoping to achieve the following:

• When no date is entered, the cell should remain with 'no fill'
• When a date is entered in the 'started' column (column H in my worksheet), and it is less than 14 days before todays date, it should be highlighted in yellow
• When a date is entered in the 'started' column and it is more than 14 days before todays date (i.e. I have been working on it for 2 weeks), it should be highlighted in red - notifying me that it needs to be followed up
• When a date, any date is entered in the 'completed' column (column I in my worksheet), it should default to green and should also change the colour in the 'started' column (from either red or yellow) to green.

Hopefully this makes sense what I am trying to achieve. Please let me know if I made any mistakes entering the formulas you have already mentioned.

Thanks again.

Answer 4

Hi HansV,

Thank you! It is working perfectly now! Your assistance with this is extremely appreciated!