Conditional Formatting with Dates

[nhays9010]

I have a training spreadsheet that I am tracking 16 different trainings for about 30 people.

I have the dates the training was complete out next to their names. All of the training is annual, so I would like to have the colors change as the exp date approaches.

If training was completed 1Jan18, up to 300 days after, I would like to keep green. At 301-350, changed to yellow, and 351+ would be red.

 

[Scott T]

For red use

=(TODAY()-$B$2)>=351

For green use. Make sure Stop if True is checked in the rules manager and it is above the yellow rule

=(TODAY()-$B$2)<=300

For yellow use

=(TODAY()-$B$2)<=350

 

[nhays9010]

Now that the formatting is correct, how do I copy this rule to the other 115 cells I need to track without pulling the rule from the original cell?

 

[dreid1011]

First you need to select all 116 cells you want the rules applied to. Then open conditional formatting and create those rules in the order Scott T suggested. Depending on the layout of your cells, change the cell reference like so:

If they are all in a single column, use this cell reference in each rule: $B2
If they are in multiple rows and columns, use this cell reference of the top left cell in the selected range: B2

The use of $ is important here.

 

[nhays9010]

Understood, my range is from D2-S33. So would my rule change to "=(TODAY()-D2:S33)<=300"

First you need to select all 116 cells you want the rules applied to. Then open conditional formatting and create those rules in the order Scott T suggested. Depending on the layout of your cells, change the cell reference like so:

If they are all in a single column, use this cell reference in each rule: $B2
If they are in multiple rows and columns, use this cell reference of the top left cell in the selected range: B2

The use of $ is important here.

 

[dreid1011]

No. The rule would change to:

"=(TODAY()-D2)<=300"

Excel conditional formatting is smart about the cell references and updates them per cell as if you manually dragged the formula across the sheet.

 

[nhays9010]

Alright, I think I got it now. Time for tomorrow to tell me if I'm a liar or not. So 300 days minus today would have been 7Jun17. That cell is still green. When I come into the office tomorrow, that 7Jun17 cell should be yellow, and my 8Jun17 cell should still be green. Hopefully I am getting all this straight.

Thanks for all the assistance guys!

No. The rule would change to:

"=(TODAY()-D2)<=300"

Excel conditional formatting is smart about the cell references and updates them per cell as if you manually dragged the formula across the sheet.

 

[dreid1011]

You're welcome. Let us know how it goes.

 

[markmzz]

I have a training spreadsheet that I am tracking 16 different trainings for about 30 people.

I have the dates the training was complete out next to their names. All of the training is annual, so I would like to have the colors change as the exp date approaches.

If training was completed 1Jan18, up to 300 days after, I would like to keep green. At 301-350, changed to yellow, and 351+ would be red.

Hi!

Try the suggestion below:

1) Select the range D2:S33 (with D2 - active cell).

2) Create de Conditional Formatting (Formula)

=(D2<"1/jan"+351)*(D2>"1/jan"+300) - color Yellow

=D2<"1/jan"+301 - color Green

=D2>"1/Jan"+350 - color Red

3) Finally, click Ok.

I hope this helps.

Markmzz