Conditional formatting for date ranges

theYaniac

theYaniac

Board Regular
Joined
Jan 7, 2018
Messages
64
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
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
 
Last edited:
Upvote 0
[TABLE="width: 989"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]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.


[TABLE="width: 989"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Today's Date:[/TD]
[TD]1/8/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Group[/TD]
[TD]Badge Number[/TD]
[TD]AK ID Number[/TD]
[TD]Driving Certification[/TD]
[TD]Forklift Certification All Terrain[/TD]
[TD]Man Lift Certification[/TD]
[TD]Respirator Certification PFT/FIT[/TD]
[TD]Confined Space Entry[/TD]
[TD]Confined Space Attendant[/TD]
[TD]LOTO Certification[/TD]
[TD]Skid Steer Certification[/TD]
[TD]Front End Loader Certification[/TD]
[/TR]
[TR]
[TD]TAR[/TD]
[TD]70777[/TD]
[TD]AK041691[/TD]
[TD]8-15-16[/TD]
[TD]8/16/2016[/TD]
[TD]9/2/2017[/TD]
[TD]11/3/2017[/TD]
[TD]8/27/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Joe4 said:
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
Click to expand...

When applying the third condition it is returning a formula error
 
Upvote 0
Looks like I just put an extra right parenthesis ")" on the end of the formula.
Just remove the last one, and it should work.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Lookup based on 2-way matching with an embedded date condition
Replies
5
Views
52
chris_bosten
C
D
  • Solved
Automation for training sheet
2
Replies
13
Views
749
David Clark
D
L
  • Solved
VBA Find Cell from Date listed, in Columns, Return cell to highlight in color
Replies
7
Views
532
mumps
mumps
M
  • Question Question
Add extra line of code to VBA to copy cell value across sheet
Replies
1
Views
75
myall_blues
myall_blues
N
  • Question Question
Auto Training Tracker w/MS Forms
Replies
6
Views
387
Snakehips
Snakehips

Forum statistics

Threads
1,224,817
Messages
6,181,148
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top