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]

Hi & welcome to MrExcel.
How about

Fluff.xlsm
	A	B	C	D	E	F	G	H
1	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
2	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
3	Tracey	Lyndon Young	Rm 2, 208 Nechells Park Road, Nechells, Birmingham, B7 5SP	20/12/2021	01/01/2023	15/05/2023
4	Tracey	Arnold Gasana	Rm 3, 208 Nechells Park Road, Nechells, Birmingham, B7 5SP	10/02/2023	01/05/2023
5
6
Sheet4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F2:F7	Expression	=E2>=TODAY()-80	text	NO
F2:F7	Expression	=F2>TODAY()-10	text	NO
F2:F7	Expression	=AND(F2<>"",F2<TODAY())	text	NO

 

[Buggy1967]

Hi & welcome to MrExcel.
How about

Fluff.xlsm
	A	B	C	D	E	F	G	H
1	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
2	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
3	Tracey	Lyndon Young	Rm 2, 208 Nechells Park Road, Nechells, Birmingham, B7 5SP	20/12/2021	01/01/2023	15/05/2023
4	Tracey	Arnold Gasana	Rm 3, 208 Nechells Park Road, Nechells, Birmingham, B7 5SP	10/02/2023	01/05/2023
5
6
Sheet4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F2:F7	Expression	=E2>=TODAY()-80	text	NO
F2:F7	Expression	=F2>TODAY()-10	text	NO
F2:F7	Expression	=AND(F2<>"",F2<TODAY())	text	NO

Thank you for the information, but I must be doing something wrong. i cant get the formula to work in my spreadsheet.. ive tried adding the information as a new conditional formatt, but it does not work. what am I doing worng?

 

[Fluff]

Can you post what you've done using the Xl2BB add-in.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Buggy1967]

Can you post what you've done using the Xl2BB add-in.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

I've not added the add in

 

[Fluff]

Then can you do that?

 

[Buggy1967]

it wont let me add the extension. it says that it cant be added in protected view. but my excel sheet is not in protected view mode

 

[Fluff]

Did you copy the file into a trusted location? Such as the normal add-in folder?

 

[Buggy1967]

sorry to be a pain but not yet. My excel skills only go to a basic level

 

[Buggy1967]

it is now added to the add in. how do i go about utilising this