Conditional Formatting for five and ten year milestones

Hi all,

I am attempting to make a sheet that tracks our five and ten year awards plus a ten year longevity. Currently I have each employee's hire date in the H column, the five year award in the J column, a ten year award in the K column, and the ten year longevity. I know there are two columns that will trip at the same time but i want to check them off once the employee recieves the award. the other part of this is I would like the cells to turn yellow when they are within a year of the milestone and red if it is past the milestone (five or ten years from hire). Thank you for your help. Matt

Welcome to the forum.

To get the 5-year date, use this in J2 and drag down:

Formula:

Please Login or Register  to view this content.

Change the +5 to +10 for the 10-year date, obviously.


To have the cells highlight as you want, do this:

1. Select your range.
2. On the Home tab of the ribbon, click 'Conditional Formatting', then 'New Rule', then 'Format only cells that contain'.
3. In the Rule Description below, select 'Cell Value' and 'less than' then enter this in the last box:

Formula:

Please Login or Register  to view this content.

4. Click 'Format' and select red fill.
5. Repeat step 2.
6. Repeat step 3 with this in the last box:

Formula:

Please Login or Register  to view this content.

7. Click 'Format' and select yellow fill.

That should do what you want.

As @Aardigspook just said thats pretty much it.
I put it into a workbook for you. Just a minor correction from what Aardigspook said. For the last CF step i took =today()+365, cause if I understood you correctly you would like the cells to turn yellow if there is one year, or less, until they reach a milestone?!

In answer to your first question - this is what we mean when we say 'drag down':

1. put the formula in J2;
2. select cell J2;
3. move your cursor over the bottom-right of the cell until it changes to a black cross (see attached picture);
4. hold the (left) mouse button down, drag down as far as you want the formula to go, then release the mouse button.

If you do the formulae in K2 and L2 first, you can select J2:L2 in step 2, to drag down all three formulae at the same time.

I think your second is actually two questions:

a. To get the formulae to give a blank if there is no date in column H, surround it with an IF statement, like this (for J2):

Formula:

Please Login or Register  to view this content.

This tells J2 to look at H2 and if H2 is not blank (<>"") then apply the Date formula. If H2 is blank, the IF formula will return a blank (from the final "" ).

b. The formatting from Conditional Formatting (CF) will always over-ride manual formatting, so you can't just turn a cell black. There are two ways you could deal with this:

(1) When the award has been given, type 'Y' or 'yes' into the cell to overwrite the formula; the CF will then not apply so the cell will be un-shaded again and you can manually colour it black (or you could use CF to colour it black if the cell contains 'Y').
(2) If you want to keep the date, then insert another column beside the award column, in which you can type a 'Y' or 'yes' (or maybe the date the award was actually given, if that's what you're doing). You can then use CF to colour the 5-yr/10-yr cell black.

For (1), use CF of 'Cell Value' 'equals' '="Yes"' (or '="done"' or whatever you want to use) then format with black fill.
For (2), the CF's a little different. I'll assume your new columns are columns K and M (to the right of the 5-yr J and 10-yr L):

• Select your J range (J2:J100 or whatever)
• Click 'Conditional Formatting', then 'New Rule', but this time choose 'Use a formula to determine which cells to format'.
• In the formula box, enter this:

Formula:

Please Login or Register  to view this content.

• Click 'Format' then choose black fill.
Repeat with your L range and M2 in the formula.

Finally, an apology: my brain obviously wasn't working correctly when I gave you my previous answer . The second CF formula should have been 'Cell Value' 'less than' '=TODAY()+365' - as anghicole correctly pointed out!


I've attached a file with all of the above working.
If anything doesn't make sense, please just ask again. Good luck.

Thanks for the rep and for marking the thread as Solved - glad we were able to help.