Changing a different cell colour depending on date

[OliveW93]

I am creating a spreadsheet with Clients info. I have their information stored in Excel but want to make their name cell change colour when I have a meeting with them on a specific date.

I have tried a few different formulas & rules, none of them are working quite right though. I'm almost there I just can't quite get it.

Example: 

I have Jane Doe's email, phone, and date of first contact as regular cells.

I meet with Jane on a date in the future & now need to input that date in her row of info - how can that date make her name change colour, whilst remaining clear without that date? I have an ! in the date cell until the meeting date is input, as it correlates to another formula, is that what's throwing the calculation off?

This rule needs to be applied to the whole column. I can get it working for just one cell but as soon as I add other cells, it stops working properly. 

I am confident in Excel but still not well versed so please use simple language. I hope this makes sense. I am using a 2019 version.

Many thanks

[#2]

@OliveW93 

Could you post a screenshot of your worksheet (without real names and data)?

Or even better, attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

[#3]

I am working on an excel document and would like to use the traffic light system an example a supervision was due April and it hasn't take place it would turn to red automatically, if one was due today it would be green but would turn yellow next week if it hadn't taken place

[#4]

@lmerrick71 

See my previous reply

[#5]

Supervision Due
Jo Bloggs 25/04/2023
so this would be red and once it took place would automatically change green

Bloggs 24/05/2023
this would be yellow but if didn't happen for a month would automatically change colour or if it took place would change to green

Jo Bloggs 25/05/2023
this would be green but tomorrow would automatically change to yellow

[#6]

@lmerrick71 

Thanks, but how do we know whether it took place?

[#7]

I would change the date to the date that the supervision took place which should then automatically change to green

so red would be well overdue (ie a month), amber just overdue (a week or so) and green would be due or taken place!

[#8]

@lmerrick71 

As far as I can tell, that wouldn't work the way you want. Let's take an example. Today is 25/05/2023.

A cell contain 20/04/2023. This is more than a month ago, so the cell is red.

Now the supervision takes place today, so you change the date to 25/05/2023. The cell automatically becomes green, as intended.

But tomorrow, 25/05/2023 is in the past, so the cell becomes yellow, and at the end of June, it would become red.

An alternative could be to enter a date in the future, for example the date the next supervision should take place, or if that is not applicable, a 31/12/2099 or so.

Or you could enter the date the supervision took place in another cell.

Do you have a preference?

[#9]

I think the option of putting the date in the future would be good as supervisions normally take place every three months

[#10]

@lmerrick71 

OK, select the cells with the dates.

On the Home tab of the ribbon, select Conditional Formatting > New Rule...

Select 'Icon Sets' from the 'Format Style' drop down.

Select Formula from both drop downs under Type.

Enter =TODAY() in the first box under Value, and =TODAY()-7 in the second box under Value.

Click OK.