How to change cells colours when an expiry date is coming to an end

[Buggy1967]

I wonder if anyone can help me I have a spreadsheet, that I need to have cells that change colour when dates are close.

Please see below. What I have been trying to do is to take the date in cell Eas a start date and make cell F change colour to green for the next 80 days and then turn amber for the 10 days prior to the date in cell F. then depending if the date in cell e has been updated the cell will either revert to green or turn red. All help with this will be very much appreciated.

Thank you in advance.

A​	B​	C​	D​	E​	F​	G​	H​
Support Worker	Resident Name	Address	TSD (Tenancy Start Date)	Risk Assessment Completion Date	Risk Assessment Review Date	Support Plan Completion Date	Support Plan Review date
Tracey	Jordon Chew	Rm 1, 208 Nechells Park Road, Nechells, Birmingham, B7 5SP	09/02/2023​	01/01/2023	01/04/2023	01/01/2023	01/04/2023
Tracey	Lyndon Young	Rm 2, 208 Nechells Park Road, Nechells, Birmingham, B7 5SP	20/12/2021​
Tracey	Arnold Gasana	Rm 3, 208 Nechells Park Road, Nechells, Birmingham, B7 5SP	10/02/2023​

 

[Fluff]

The instructions are on the page I linked to.

 

[Buggy1967]

Thank you for your help and understanding when dealing with a novice

 

[Buggy1967]

My issue now is that I have come to the decision that I don't know how to input the formulas into the excel sheet so that they will work. if its possible to get a step by step guide it would be really appreciated.

 

[Fluff]

Select F2 down to the end of the data & on the Home tba click Conditional formatting, New rule, use a formula & then enter the formula into the box.

 

[Buggy1967]

Select F2 down to the end of the data & on the Home tba click Conditional formatting, New rule, use a formula & then enter the formula into the box.

Thank you

 

[Fluff]

You're welcome.

 

[Buggy1967]

Hi me again, god knows what Ive done wrong this time. but Ive added all the formuals however the cell colours in F$ do not change when I change the date in cell E4.

any advice guidance will be greatly received.

the formulas are below

 

[Fluff]

Both images show the same date, so it's difficult to tell.
Also you should have selected F4 to the end of the data before applying the formatting.

 

[Buggy1967]

i have no idea what im doing wrong but I cant get these formulas to work to change the cell colours