Forum Discussion
Color coding
Hi everyone!!!
i am a training coordinator so I have to keep track of a lot of dates. What i am wondering is if there is a way to code cells so that if it is almost a year from the date (about a month before), it turns yellow and if it is a year from the date in the cell it turns red.
i have done it for a date that is relative to to todays date, but not predicted like i want.
any suggestions?
Thanks!!!
12 Replies
You say you've "done it for a date that is relative to to todays date, but not predicted like i want."
So you may already be close to your answer. I assume you did it using "Conditional Formatting." And that would be the way to do it. You can just put two different conditions into the dialog box that controls the format.
Here's a screen capture of a conditional formatting rule I have that turns the dates in a cell to red background and red text if the month displayed in cell $M$4 is greater than the date in $U$1. In my case, this just means it's in the future.
You could have such a rule comparing current cell's value to a cell containing the formula =TODAY()+365 (which would be one year in the future), You condition would simply read "Cell Value < $U$1" , replacing $U$1 with whatever the address is for your reference cell. And you could add another condition that makes it yellow when comparing to a cell that contains the formula =TODAY()+395
Here: I've done a sample for you in the attached. Here's the set of conditions.
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?"
