Help with complicated Conditional Formatting

[db321]

Hi, I am in the process of creating an MS Excel spreadsheet to track Accreditation Authorization Termination Dates (ATD). I have a number of columns with different information types. In column C has the ATDs, column J has the start date for when I need to start assessing security controls, and column K has the end date for when the assessment should end. I want column J to automatically turn green 30 days before the assessment start date, then automatically turn yellow on the start date, and automatically turn red if the assessment date in column has passed. I have been able to get the cell to turn color, if I manually change the date in column J. But have not figure out to have it change automatically based on dates. Column E has the Assessment Types, (Re-accreditation, Annual Review, or New). For systems that are going through an Re-accreditation and did not do an annual review, I am giving myself 90 days to complete the assessment of 1/3 of the controls, for a total of three assessment dates. And the annual reviews 1/3 of the controls are assessed annually.

Any help would be greatly appreciated.

Daryl

 

[etaf]

I want column J to automatically turn green 30 days before the assessment start date, then automatically turn yellow on the start date, and automatically turn red if the assessment date in column has passed.

Conditional format should work - ALL based off column J the start date for Green & yellow
Then column K for RED
BUT what column has that the info that the assessment has been done - so you can ignore that row ?

NOT sure i follow this bit or what cells to use

Column E has the Assessment Types, (Re-accreditation, Annual Review, or New). For systems that are going through an Re-accreditation and did not do an annual review, I am giving myself 90 days to complete the assessment of 1/3 of the controls, for a total of three assessment dates. And the annual reviews 1/3 of the controls are assessed annually.

i have done the green & yellow
BUT on RED are we testing column K? AND how to tell if complete , otherwise will all just go RED

Book2
	J
1	date
2	9/26/20
3	9/27/20
4	9/28/20
5	10/28/20
6	9/30/20
7	9/26/20
8	10/2/20
9	9/26/20
10	10/4/20
11	10/28/20
12	10/6/20
13	10/7/20
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J:J	Expression	=J1=TODAY()	text	NO
J:J	Expression	=AND(J1>=TODAY()-30, J1<TODAY())	text	NO