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

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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

D
  • Solved
Automation for training sheet
2
Replies
13
Views
732
David Clark
D
L
  • Solved
VBA Find Cell from Date listed, in Columns, Return cell to highlight in color
Replies
7
Views
497
mumps
mumps
N
  • Question Question
Auto Training Tracker w/MS Forms
Replies
6
Views
371
Snakehips
Snakehips
R
  • Question Question
Conditional Formatting based on dates.
Replies
8
Views
181
MARK858
MARK858
H
  • Question Question
Expiring Date formula?
Replies
5
Views
189
Alex Blakenburg
A

Forum statistics

Threads
1,223,888
Messages
6,175,219
Members
452,620
Latest member
dsubash

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