Conditional formatting for date ranges

[theYaniac]

I am working on building a simple database for employee training records. Some of our specific training must be renewed on an annual basis. I am trying to format cells to change color when a date exceeds a specific amount of time. At the top of the data sheet I have a cell that displays the current date. Within the employee records are the actual dates the training was completed. I would like for the cells to remain green if the training is current, yellow if the training will expire in 30 days or less and change to red if the training has expired. Is this possible? Any help would be greatly appreciated.

 

[Joe4]

You can do it with a few Conditional Formatting Formulas.
Let's say that your current date is in cell A1, and the dates you are checking are in A2:A100.

For first condition: expired
- Select cells A2:A100
- Enter this Conditional Formatting formula: =$A2 < $A$1
- Choose your Red color option

For the second condition: upcoming due
- Select cells A2:A100
- Enter the CF formula: =AND($A2 >= $A$1,$A2 <= ($A$1+30))
- Choose your Yellow color option

For the third condition: current
- Select cells A2:A100
- Enter this Conditional Formatting formula: =$A2 > ($A$1+30))
- Choose your Green color option

 

[theYaniac]

I have been unsuccessful in my attempts to make the formula you referenced work. For the shown example, 1-8-18 is in cell K2, the dates below each certification are in row 8. Each row is the records for an individual employee which fill columns L through T. I can get the cells to turn red, but I cannot get the others to work. Today's Date: 1/8/2018 Group Badge Number AK ID Number Driving Certification Forklift Certification All Terrain Man Lift Certification Respirator Certification PFT/FIT Confined Space Entry Confined Space Attendant LOTO Certification Skid Steer Certification Front End Loader Certification TAR 70777 AK041691 8-15-16 8/16/2016 9/2/2017 11/3/2017 8/27/2017 <colgroup><col><col><col><col><col><col><col><col><col><col><col span="2"></colgroup><tbody> </tbody>

<colgroup><col><col><col><col><col><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[Joe4]

So it sounds like that you want to apply this to the range L8 out to column T down to the last row.
So, select the range L8 down to the last row in column T with data, and use these formulas:

1st condition:
=L8 < $K$2
2nd condition:=AND(L8 >= $K$2,L8 <= ($K$2+30))

3rd condition:
=L8 > ($K$2+30))

Note that this will only work if all your dates are entered as dates (and not text). One easy way to check is to try to change the date format of the cell. If that has no effect on the cell, then you have text entries and not actual date entries.

 

[theYaniac]

Thank you

 

[Joe4]

You are welcome!

The key thing is understanding when to use Absolute vs. Relative Range references. You put the dollar sign in front of the row and column references you want to lock down (or leave them off to let them "float" as your range moves).
See: http://www.cpearson.com/excel/relative.aspx

 

[theYaniac]

You are welcome!

The key thing is understanding when to use Absolute vs. Relative Range references. You put the dollar sign in front of the row and column references you want to lock down (or leave them off to let them "float" as your range moves).
See: http://www.cpearson.com/excel/relative.aspx

When applying the third condition it is returning a formula error

 

[Joe4]

Looks like I just put an extra right parenthesis ")" on the end of the formula.
Just remove the last one, and it should work.