SOLVED

Conditional formatting for dashboard

Copper Contributor

Excel Office Professional Plus 2016

 

Attached is a list of the healthcare patients at my work. I am trying to create a rudimentary dashboard to keep track of patient documentation. I want to use the conditional formatting to signal when a patient's paperwork needs to be updated. The far right 2 columns are red. These are the columns I want to apply conditional formatting to, using a specific rule.  I cannot figure out how to create the rule I need.
 
 All of these patients have to sign a Consent to Treatment (CNST) and have an Assessment (SSMT) done every year. The columns contain the dates these documents were first completed. These documents expire after 365 days, and need to be redone. I want to create a rule in the spreadsheet that will highlight the cell in YELLOW when the document is 30 days from expiring (335 days after the date in the cell), and turn it RED after it has expired (365 days after the date in the cell.)  Then, once the document is updated, I will go into the sheet and enter the new date, and the cell should go back to white. After another 335 days, the cell would turn yellow again. 
Is what I am trying to accomplish clear? I want to be able glance at the spreadsheet and see who needs a new consent and assessment done. I have 100+ people to keep tabs on so making it easy and visual for myself and my staff is a big help.  (Thank you for your time!)
 
Based on my limited knowledge of excel, the formula would read something like:
 
If TODAY is greater than (Cell Value) + 330 days than make the cell Yellow
If TODAY is greater than (Cell Value) + 365 days than make the cell Red
 
PNT#INTLSTAF CNSTSSMT
233 Steve 10/07/2110/07/21
1578 Steve 09/27/2009/27/20
1674 Steve 10/08/2010/08/20
3143 Steve 12/29/2012/29/20
4027 Steve 01/21/2101/21/21
4533 Steve 10/22/2110/22/21
2 Replies
best response confirmed by generatorsteve (Copper Contributor)
Solution

@generatorsteve 

See the attached sample workbook.

Look at Conditional Formatting > Manage Rules... to see how it's done.

1 best response

Accepted Solutions
best response confirmed by generatorsteve (Copper Contributor)
Solution

@generatorsteve 

See the attached sample workbook.

Look at Conditional Formatting > Manage Rules... to see how it's done.

View solution in original post