Conditional Formatting by Date and Applying a Frequency to provide a Traffic Light

[Jiffy11]

Hi,

I am trying to produce a traffic light (conditional formatting) RAG using a date and then applying a Frequency to determine the overall status of the RAG whilst applying the following rules:

RAG Status = Column D (apply =TODAY()) which I apply to each cell within column D within the table.

Conditional Formatting Rules:

If the Date = Column C is greater than the Frequency = Column B = RED
If the Date = Column C is 1 month less than the Frequency = Column B = AMBER
All other instances = GREEN

Traffic Light.PNG

I hope that makes sense

[CAntosh]

Welcome to the forum!

I'm not sure I fully understand what you're aiming for. It would have been very helpful if you could have provided a sample workbook; they tend to be far more useful than images. Nevertheless, my interpretation of your request is that you're looking for a conditional formatting setup that returns an amber icon if the date is within the forthcoming month, a red icon if it has passed, and a green icon if it is more than a month in the future. You mention 'frequency', but my understanding is that the frequency helps determine what the date in column C(?) is, so the icon is based solely on the date in C and today, and not directly on the listed frequency. If I'm close, then you can use the following formula in the RAG column (D2 in my attachment):

=IF(EDATE(C2,-1)>TODAY(),3,IF(C2>=TODAY(),2,1))

This formula will return a 3 if it should be green, a 2 if it should be amber, and a 1 for red. Apply the CF accordingly. Take a look at the attachment to see if I've got it right. If I don't, please clarify what you're looking for and attach a sample workbook using: Go Advanced --> Manage Attachments to get to the upload window.

[Jiffy11]

Hello,

Thanks for your help and assistance.

For some reason I do not appear to be able to upload a workbook, although I could download your workbook.

I am a bit of a novice at Excel so excuse me if I am not explaining things correctly.

Your conditional formatting does not take into account the frequency by months which could be 3, 6, 9 or 12 months for example and hence a date (today's date) must check the frequency to determine it's RAG status as the frequency will determine when something is required for review or non-compliant.

Icons.PNG

An example I used previously was:

Red =EDATE($C$4,$B$4+3)
Amber=EDATE($C$4,$B$4+1)

Difference in this instance is the RAG is Red = +3 months and Amber = +1 month from today's date.

I hope this helps.

[CAntosh]

Where I'm lost is that I don't understand how your columns relate to one another, which is where a sample workbook would be helpful. Where do the dates in column C come from? Are they manually entered, or are they in some way based on an earlier date combined with column B (as was my interpretation).

My understanding aside, though, I think the easiest approach to using the stoplight is to avoid using formulas in the RAG settings. Put a formula in the cells in column D that will return a 3 when you want green, a 2 when you want yellow, and a 1 when you want red, then just set the RAG rules to those values and check the box to show only the RAG result in the cell.

If you're still stuck and can't post a sample workbook, please walk through a specific example to show how your expected result comes from the specific information available so that I/we can better understand how your "frequency" contributes, e.g. "If TODAY() is within 12 months (B2) of 23/8/2017 (C2), then return an amber light in D2"


EDIT: Maybe it would help to show some examples and expected results in which the frequency isn't always 12?

[Jiffy11]

Hi,

Ok let me explain.

The date field Column C is a manually applied date, this date determines the RAG status based upon the frequency (Column B).

The Frequency (Column B) determines the frequency of when the documentation, test etc Column A needs to be reported upon by the RAG status.

Some data is 3 months, some 6 months, 9 months etc, hence why the RAG has to take in the frequency.

Example attached that looks at a RAG that is

+1 Month of column C = Amber
+3 months of column C = Red

Does this help?

[CAntosh]

There's some hurdle in my brain that is simply not being cleared. The sample uses one "green" example that's right next to today and would be green under nearly any interpretation of your objective, while the "red" examples are so old that they would be red under any interpretation, so I can't really dissect how your 1 month/3 month rules fit with the frequency. Is the following interpretation correct?

"If the DATE plus the FREQUENCY is within 1 MONTH of TODAY, then green."
"If the DATE plus the FREQUENCY is within 3 MONTHS of TODAY, then amber."
"Otherwise... red."

or is it;

"If TODAY plus the FREQUENCY is within 1 MONTH of the DATE, then green."
"If TODAY plus the FREQUENCY is within 3 MONTHS of the DATE, then amber."
"Otherwise... red."

If those are both wrong, could you provide a similar set of clearly defined rules that includes the terms "Frequency", "Date", "today", and "1 month/3 month rule"?

[Jiffy11]

Hi,

I think I have solved the puzzle, after I reviewed your logic.

Traffic Lights.PNG

Can you check my logic to see if I have applied what I explained.

Hopefully this will enable you to sleep better and less troubled by answering my query.

[CAntosh]

haha, I'm still not sure what logic I contributed, but if it somehow helped you find a solution, I'm content with that success. Based on what I see in your attachment, it looks like you're currently applying:

- red light if today is past the date in C plus the months in B
- amber light if today is within two months of the date in C plus the months in B
- green light if today is not yet within two months of the date in C plus the months in B

If that's what you're looking for, then it looks like you've got it!

EDIT: Wait... no. What I've described above only applies to row 2. You have a different rule for every row? Let me re-evaluate...

[CAntosh]

Looking at your attachment further, I think your approach is problematic. Writing a different rule for each row is inefficient. As I mentioned above, I think it's wiser to write a formula for the cells themselves that returns a "3" for green, "2" for amber, and "1" for red. You can then use one formatting rule for the entire range. In the attachment, I use the following formula in E4, then fill it down:

=IF(EDATE(C4,B4)>EDATE(TODAY(),1),3,IF(EDATE(C4,B4)>TODAY(),2,1))

You can then apply the standard RAG rule to the entire E4:E9 range. This approach returns:

- Green if the Date in C plus Months in B is more than one month away from today
- Amber if the Date in C plus Months in B is coming within a month of today
- Red if the Date in C plus Months in B has passed

I'm hoping this interpretation is close to the mark?

[Jiffy11]

CAntosh, I would like to thank you for your help with my conditional formatting issues that you helped me resolve recently. Sorry the long delay in responding back to you life became a little bit hectic. Appreciate your support and perseverance in trying to understand my requirements and find a solution that works.

[CAntosh]

I'm glad I could help, good luck!