Conditional formatting for review dates issue

[spartikoos91]

Hi, im no pro at excel and would be grateful for any help in how to make what i need.
I have attached an image for reference but what im trying to make is a sheet where for documents named in the top row, we input the date we implemented it, then the cell below indicates the due review date - typically 6 months from the "implementation date".
I would like the "review due" date to colour code green if the document is still within 4 months of lifespan of todays date (cell A1), amber from 4 months to 6 months, then red from 6 months on. I appreciate this might be days instead like 6 months = 180 (for days ect).
And where no data has been entered in the implementation date, just for the cell to remain empty and empty in the review due cell also,

In cell A1 i have the formula =TODAY()
In the "review due" row of cells, i have the formula as =B2+180 and so on for B3, B4 ect.

please help. it would make a massive difference to care homes i oversee

 

[CSmith]

Variables

MrExcel-1235436.xlsx
	A	B	C
1	Green Offset	120	<-- 2/3 days of review
2	Amber Offset	180	<-- Total Review Days (after 2/3 days+1)
3	Red Offset	181	<-- Review Due +1 day
Variables

Cell Formulas
Range	Formula
B1	B1	=INT(ReviewOffset/3*2)
B2	B2	=ReviewOffset
B3	B3	=ReviewOffset+1

Named Ranges
Name	Refers To	Cells
ReviewOffset	=spartikoos91!$A$3	B1:B3

Main Sheet

MrExcel-1235436.xlsx
	A	B	C	D	E	F	G	H
1	21/04/2023	DOCUMENT 1	DOCUMENT 2	DOCUMENT 3	DOCUMENT 4	DOCUMENT 5	DOCUMENT 6	DOCUMENT 7
2	IMPLEMENTATION DATE	20/10/2022	14/04/2023	10/09/2022	11/01/2021	17/11/2021		10/11/2022
3	REVIEW DUE *ENTER DATE OFFSET IN THIS CELL*	18/04/2023	11/10/2023	09/03/2023	10/07/2021	16/05/2022		09/05/2023
4		-3	173	-43	-650	-340		18
5	Using named cells in variables tab for the	Red	Green	Red	Red	Red		Amber
6	conditional formatting rules
7	All formulas are dynamic based on review due above
8	Assumption is for reviews to be done on or before
9	TODAY and review due dates greater than 60 days
10	from today are greens, days of 60-0 should be amber
11	and <0 should be red
spartikoos91

Cell Formulas
Range	Formula
A1	A1	=TODAY()
B3:H3	B3	=IF(NOT(ISBLANK(B2)),(B2)+ReviewOffset,"")
B4:H4	B4	=IFERROR(B3-TodaysDate,"")
B5:H5	B5	=IFERROR(IF(OR(B4="",ISBLANK(B4)),"",IF(B4>(ReviewOffset-GreenOffset), "Green",IF(B4>ReviewOffset-AmberOffset,"Amber","Red"))),"")

Named Ranges
Name	Refers To	Cells
AmberOffset	=Variables!$B$2	B5:H5
GreenOffset	=Variables!$B$1	B5:H5
ReviewOffset	=spartikoos91!$A$3	B5:H5, B3:H3
TodaysDate	=spartikoos91!$A$1	B4:H4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:H3	Expression	=OR(ISBLANK(B3),B3="")	text	YES
B3:H3	Expression	=IF(B3-TodaysDate>ReviewOffset-GreenOffset,TRUE,FALSE)	text	YES
B3:H3	Expression	=IF(B3-TodaysDate>ReviewOffset-AmberOffset,TRUE,FALSE)	text	YES
B3:H3	Expression	=TRUE	text	NO

 

[spartikoos91]

CSmith, thank you very much, its going to take me a few days to process this, looks terrifying but seems to be the solution. much appreciated.

 

[CSmith]

No problem, very simple I can send you a link to the file if you like?

 

[spartikoos91]

No problem, very simple I can send you a link to the file if you like?

Hello, if you could that would be appreciated.
Im currently on the spreadsheet trying work out how you actually did this and where but not having much luck, i really appreciate it.

 

[CSmith]

MrExcel-1235436.xlsx

Microsoft Excel Workbook

1drv.ms