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?"