30/60/90 day tracking

[SFCChase]

Hoping somebody out there can help me out! I've recently been moved into an office position that requires me to track numerous trainings for my employees. I have a digital leaders book that holds all my information, but I'm hoping to use Excel to make things a little simpler and provide me with an "at-a-glance" ability.

What I'm basically looking to do is calculate and color cells based on dates. I would like to be able to enter an expiration date in one cell and have a second cell turn green (if greater than 30 days), yellow (if 0 to 29 days), or red (if >0 days) based on the # of days from today to the expiration date.

So in the sheet below I would like cell E5 to have a date....and cell E6 to turn green, yellow, or red depending on the lenght of time the expiration date is from today.
Ideally >30days would be green
0-29days yellow
<0days would be red.

Any help would be appreciated!

[TABLE="width: 548"]
<TBODY>[TR]
[TD="class: xl64, width: 15, bgcolor: gray"]a12
[/TD]
[TD="class: xl64, width: 159, bgcolor: gray"]3
[/TD]
[TD="class: xl64, width: 241, bgcolor: gray"]4
[/TD]
[TD="class: xl64, width: 75, bgcolor: gray"]5
[/TD]
[TD="class: xl64, width: 75, bgcolor: gray"]6
[/TD]
[TD="class: xl64, width: 166, bgcolor: gray"]7
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: gray"]b
[/TD]
[TD="class: xl69, bgcolor: #eeece1, colspan: 2"]TABLE OF CONTENTS
[/TD]
[TD="class: xl64, bgcolor: gray"][/TD]
[TD="class: xl64, bgcolor: gray"][/TD]
[TD="class: xl64, bgcolor: gray"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: gray"]c
[/TD]
[TD="class: xl68, bgcolor: #948b54, colspan: 5"]Medical Protection Systems
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: gray"]d
[/TD]
[TD="class: xl75, bgcolor: #c5be97"]Full Name and Rank
[/TD]
[TD="class: xl67, bgcolor: #c5be97"]Item
[/TD]
[TD="class: xl67, bgcolor: #c5be97"]Expiration
[/TD]
[TD="class: xl67, bgcolor: #c5be97"]Status
[/TD]
[TD="class: xl67, bgcolor: #c5be97"]Scheduled Appointment
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: gray"]e
[/TD]
[TD="class: xl65, bgcolor: #ddd9c3"][/TD]
[TD="class: xl66, bgcolor: #ddd9c3"]Periodic Health Assessment
[/TD]
[TD="class: xl73, bgcolor: #ddd9c3"][/TD]
[TD="class: xl65, bgcolor: #ddd9c3"][/TD]
[TD="class: xl65, bgcolor: #ddd9c3"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: gray"][/TD]
[TD="class: xl64, bgcolor: gray"][/TD]
[TD="class: xl66, bgcolor: #ddd9c3"]Deployment Health Assessment
[/TD]
[TD="class: xl73, bgcolor: #ddd9c3"][/TD]
[TD="class: xl73, bgcolor: #ddd9c3"][/TD]
[TD="class: xl65, bgcolor: #ddd9c3"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: gray"][/TD]
[TD="class: xl64, bgcolor: gray"][/TD]
[TD="class: xl66, bgcolor: #ddd9c3"]Post Deployment Health Assessment
[/TD]
[TD="class: xl73, bgcolor: #ddd9c3"][/TD]
[TD="class: xl73, bgcolor: #ddd9c3"][/TD]
[TD="class: xl65, bgcolor: #ddd9c3"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: gray"][/TD]
[TD="class: xl64, bgcolor: gray"][/TD]
[TD="class: xl66, bgcolor: #ddd9c3"]Dental Readiness
[/TD]
[TD="class: xl73, bgcolor: #ddd9c3"][/TD]
[TD="class: xl73, bgcolor: #ddd9c3"][/TD]
[TD="class: xl65, bgcolor: #ddd9c3"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: gray"][/TD]
[TD="class: xl64, bgcolor: gray"][/TD]
[TD="class: xl72, bgcolor: #ddd9c3"]Vision
[/TD]
[TD="class: xl74, bgcolor: #ddd9c3"][/TD]
[TD="class: xl74, bgcolor: #ddd9c3"][/TD]
[TD="class: xl71, bgcolor: #ddd9c3"][/TD]
[/TR]
</TBODY>[/TABLE]

 

[SFCChase]

Anybody????

 

[Brian from Maui]

Have you tried using Conditional Formatting and selecting Formula Is?

=(A1-TODAY())<0

=(A1-TODAY())<=29

=(A1-TODAY())>=30

 

[SFCChase]

When I click on Conditional Formatting, there is no Formula is option. Any other suggestions?

 

[Brian from Maui]

I'm using 2003, so if you're using a newer version see the Help Files