SOLVED

Conditional formatting traffic lights date and text

%3CLINGO-SUB%20id%3D%22lingo-sub-2281587%22%20slang%3D%22en-US%22%3EConditional%20formatting%20traffic%20lights%20date%20and%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2281587%22%20slang%3D%22en-US%22%3E%3CP%3EElitebook%2F%20Windows%2010%3C%2FP%3E%3CP%3EMicrosoft%20365%20Apps%20for%20enterprise%3C%2FP%3E%3CP%3EExcel%20Version%202008%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'd%20like%20to%20create%20a%20traffic%20light%2F%20RAG%20for%20the%20attached%20worksheet%3A%3C%2FP%3E%3CP%3E1.%20Green%20%3D%20when%20Column%20A%20and%20Column%20C%20are%20both%20have%20entries%20i.e%20Date%20and%20Complete%20or%20Complete%20-%20date%3C%2FP%3E%3CP%3E2.%20Amber%20%3D%20when%20Column%20A%20has%20an%20entry%20and%20Column%20C%20is%20blank%20i.e%20Date%20is%20%26lt%3B%205%20working%20dates%20of%20current%20date%3C%2FP%3E%3CP%3E3.%20Red%20%3D%20when%20Column%20A%20exceeds%20the%20current%20date%20and%20Column%20C%20is%20blank%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2281587%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2282294%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20traffic%20lights%20date%20and%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2282294%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1028411%22%20target%3D%22_blank%22%3E%40Felicity2021%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESee%20the%20attached%20version.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2282251%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20traffic%20lights%20date%20and%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2282251%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWill%20you%20break%20the%20formula%20down%20so%20that%20I%20can%20include%20it%20within%20Column%20A%20and%20be%20able%20to%20use%20it%20as%20a%20basic%20RAG%20to%20highlight%20the%20cells.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2281621%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20traffic%20lights%20date%20and%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2281621%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1028411%22%20target%3D%22_blank%22%3E%40Felicity2021%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESee%20the%20attached%20version.%20I%20used%20a%20formula%20in%20column%20D.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Elitebook/ Windows 10

Microsoft 365 Apps for enterprise

Excel Version 2008

 

I'd like to create a traffic light/ RAG for the attached worksheet:

1. Green = when Column A and Column C are both have entries i.e Date and Complete or Complete - date

2. Amber = when Column A has an entry and Column C is blank i.e Date is < 5 working dates of current date

3. Red = when Column A exceeds the current date and Column C is blank

3 Replies

@Felicity2021 

See the attached version. I used a formula in column D.

@Hans Vogelaar 

Thank you.

 

Will you break the formula down so that I can include it within Column A and be able to use it as a basic RAG to highlight the cells.

best response confirmed by Felicity2021 (New Contributor)
Solution

@Felicity2021 

See the attached version.