Forum Discussion
Color coding
Thank you so much for your reply. I think that is what I have already done. But I had to put the date it was going to expire not the date it was given.
To be more specific, I have training's that have to be redone every year, but they are all done all at different times. So I want to be able to put in the date we did the training and then have it turn yellow a month before that year is up and red if it is already a year past that date. The way this is formatted it seems like I have to put when it will expire. Which would mean an extra column, which I can do but seems like unnecessary work.
Here is an example of what I have done:
Here's a greatly improved way to handle it, greatly improved because you can easily change the parameters at will. Instead of hard-coding the number of days, I've made those variables that are outside of the Conditional Formatting box. As currently set up, the date of CPR training in the past, will remain green until 30 days short of a full year, at which point they'll turn yellow; then, after a year has passed they'll turn red. But if you want to give yourself (and your trainee charges) more advanced warning, you can easily just change the visible variable for yellow to a smaller number of days, say 305 instead of 335; then it'll turn yellow with two months to go instead of just one. And so forth.
The formulas in the Conditional Formatting box are variations on this =TODAY()-$E$15
which is the one that turns the cells red. All you need to do to vary the parameter, then, is change the value in cell E15. And employing the TODAY() function makes today's date always the fixed point of comparison, which is what you want; the sole question being "how far FROM today?"
thank you so much!! i feel like there may be something I am missing... I did some test dates to try it out and make sure i get it right but this is what I'm getting.... I was highlighting the cells i wanted the rule to apply to and then managing the rule. is that the right way to do that?
If Sergei's suggestion has resolved it for you, that's great!
It did occur to me that your task--to see where compliance with a requirement has lapsed or is about it lapse--might be well served by showing blank dates as red.
If your worksheet persists in not serving your needs, I'd ask that you upload the actual sheet (not just an image), after first removing any personal names or other identifiable characteristics. It's a lot easier to diagnose when you're with the patient directly, not just hearing a second hand report.
thank you everyone!!
what I dont understand is why the rest of the dates are not turning yellow or red when I have dates in the past for practice.
I attached my sample file I am using to figure this out.
To exclude blank values which are considered as zeroes and always less than any date, you may add one more rule on your range with let say Cell value <2. Don't apply any format to that rule, put it on very top and check Stop if true.
