Conditional formatting (I think)

[Johnny247]

Hi all.
I could do with some help please!
I am trying to put a training file together, and want the date to be colour coded (red,amber,green) dependant on the frequency of training refresher.
As an example, if two people did a course on 10/10/2018, and the frequency is 12 weeks, then the cell is red, change the drop down box to 12 months, it goes green etc.
Hopefully that makes sense! The range is 12 weeks, 12 months, 3 years, 5 years, NA.
I have attached a picture (can't post the file on attachments?) so hopefully it will make more sense, if anyone can help?
I think it would be conditional formatting using a formula with nested If statements less than/greater than each condition, but I am not sure, and I would probably end up with some ridiculously long formula!
Thanks in advance for any help!! Capture.JPG

[BadlySpelledBuoy]

Do you mean it turns red because the refresher date has passed and it's green whilst it's in the future still?

BSB

[Johnny247]

Hi, yes, that's exactly it!
Thanks

[BadlySpelledBuoy]

Have a look at the attached. I've used a small lookup table on Sheet2 to calculate the number of days. This saves the nested IF approach which would be a lengthy formula indeed.

Two conditional formatting rules. Both using a formula to determine the outcome:

Red - =AND(C8<>"",TODAY()>C8+VLOOKUP(C$5,DaysLookup,2,0))
Green - =AND(C8<>"",TODAY()<=C8+VLOOKUP(C$5,DaysLookup,2,0))

Personally I'd add in another one to show the refresher is due within X number of days as amber.

Hope that helps.

BSB

[Johnny247]

That's it exactly!! Thanks very much for the help.
What would an amber formula look like for within 30 days please?

[BadlySpelledBuoy]

Amber would be:
=AND(C8<>"",TODAY()>C8+VLOOKUP(C$5,DaysLookup,2,0)-30)
You need to make sure the three rules are in the correct order though. Red/Amber/Green.
See attached.

BSB

[Johnny247]

Thanks once again for your help.!

[BadlySpelledBuoy]

No problem. Happy to help.

Don't forget to mark the thread as SOLVED if you're happy you have a working solution.

BSB