Conditional Formatting Expiry Date

M

manxy

Joined
Jul 18, 2009
Messages
1
Reaction score
0
Being fairly new to this, I'm looking at making things a little easier at work and need some help in how to do the following:

I would like a column D3 to D48 to indicate when a persons First aid training expires using a background colour like that of a traffic light. For instance, say i got trained today 18-Jun-09, in exactly 10 months time, the background colour would turn yellow and a month later, it would turn orange. After 12 months and over (18-Jun-10 & more), the colour would turn red

Also, peoples dates are all different and therefore whatever the training date thats entered onto the D Column for that person, the expiry date would run from then.

I was informed the following might work, but not so far anyway

=$D3:D48<DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())) [red]
=$D3<DATE(YEAR(TODAY()),MONTH(TODAY())-11,DAY(TODAY())) [orange]=$D3<DATE(YEAR(TODAY()),MONTH(TODAY())-12,DAY(TODAY())) [yellow]

Can you help?
Thanks, Manxy
 
Hi,
Here i have presumed with date value is at cell A33

Try this:
Excel > Format > Format conditional ... (that is a function full of resources).
Condition 1 > Formula Is > =$A$33<DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())) 'Then select Format and change pattern colour to Red
Condition 2 > Formula Is > =$A$33<DATE(YEAR(TODAY()),MONTH(TODAY())-11,DAY(TODAY())) 'Then select Format and change pattern colour to Orange
Condition 3 > Formula Is > =$A$33<DATE(YEAR(TODAY()),MONTH(TODAY())-12,DAY(TODAY())) 'Then select Format and change pattern colour to Yellow

you can:
whether choose a pre-existing condition
create a condition using your own formula from the extensive library of Excel.

Hope this helps
 
Back
Top