Excel Conditional Formatting icon sets with a relative reference formula

This shows a way to use conditional formatting icon sets with a relative reference formula.

---

Closed Caption:

here's a situation that is printing with
different due dates for projects
what you need is a way to use
conditional formatting with icon sets
based on relative reference formula to
identify proximity to today's date i'm
using excel 2010 and if i come up to
conditional formatting I see all these
pre-programmed conditional formatting
tools that Excel has under icon sets
that come down over here see these
different choices under shapes they see
traffic lights
this is what I want to use I want to be
able to put a circle colored circle next
to each day based on its proximity to
today's day and whether or not if the
project is finished or not
however I can't use this tool because
this tool will not let you use a
relative reference formula so i'm going
to show your work around if this is ever
your situation so here are my rules
right here in this column gonna put my
circles so I want a red circle if this
is true if this date is behind today's
date or pass to I want a red circle if
this column has a one I want a black
circle if this if the day is 60 days out
from today's day I want yellow if it's
90 days out i want to grain it if it's
between this range I want it blank
so what I need to do is determine find a
font that is a solid circle have the
no.1 web dings a small end is a solid
circle so that's what I'm going to use
also i notice that some of these don't
have dates there are no dates I want
nothing to be put in here so I'm going
to need a formula in this cell i'm going
to say it
this day is blank then leave this cell
blank if not then put in a small and
here we go my small and because there is
a date here I got to change this to web
thing so I come up here I slide all the
way down find web dings and there we go
solid circle the next thing I need to do
is write a formula for these rules
so based on these rules right here i
need a formula that is going to return
these numbers and I've already done that
i have it hidden right here on all on I
didn't show it to you it's a long if
then the very first thing it's going to
do is check this column the seasons
finished or not if it finds a one is
going to put three in can return it
three if not it's going to keep going
and then it's going to go through these
one by one looking at these different
timeframes and returning the correct
number here is returning for here it's
going to return to and song so this day
5 15 has passed already
it's behind today's date so it correctly
return before then the last thing I'm
going to do here is use conditional
formatting on this cell relative to the
result of this formula make sure that
here we go so if f 12 equals 0 color the
font white that this rule down here if
it returns a 18 green to yellow for red
I don't need three four black because
black is the default color that's the
color it is anyway so now i have to do
is drag this down its dependent on this
other formal so it's not going to really
work until I call this one down and
there we go
I don't need this to show so i can hide
it and now i have my traffic lights
calling attention to these dates just as
I wanted and I'm using a relative
reference formula me show you how to do
that
this formula is kinda long so it drops
down to the next row and that is a way
to solve this need