[Music]
well hello and boom-shaka-laka you know
a question I get asked a lot when I'm
teaching a live Excel class is there a
way to show in a date field when
something is either expired or past due
where something is becoming getting
close to an expiration date or a due
date or when something is comfortably
not even close to being expired or due
so there is a great way to use a feature
in Excel called conditional formatting
to do that for you and I just wanted to
show you two quick ways to do it here in
Excel so what I have is I have two
columns here I've just labeled one
expiration date and another one due date
but they're the exact same dates and the
parameters I'd like to set up are that
if today is if the expiration date is
expired
that is earlier than today then I want
to initially shade the expiration date
field with a red if it's within 30 days
of expiring I want to shade it with a
yellow and if it's beyond 30 days or if
the expiration date is 30 days or longer
in the future I just want to go ahead
and shade that with green so we're gonna
do that first so when I highlight this
group of cells here now we could
highlight an entire column so that you
wouldn't have to do this or expand the
range whenever you needed to do it but
I'm just going to stick with doing these
three cells right here so I'll go ahead
and highlight those I'll go up to the
conditional formatting button I'm going
to drop down here to highlight cell
rules and I'm gonna go ahead and pick
the less than condition for my first
condition so I want to say that I want
to format cells that are less than
equals today left paren right paren and
I'm going to highlight those as being
expired so I'm going to use light red
fill and dark red text so that condition
is done now we need to do our yellow
condition so with the same range we're
gonna go layer in a second condition
we're going to go to highlight cell
rules and we're going to do the between
Dean condition we want to format cells
that are between equals today and the
upper parameter is going to be equals
today plus 30 we're going to highlight
those cells as yellow phil and dark
yellow text so those would be the ones
that are within 30 days of expiring and
finally we're going to go to conditional
formatting and we're going to pick a
greater than condition and we're going
to say we want to format cells that are
greater than equals today plus 30 with
green fill and dark green text and so
there you have it now
anything that's expired that is where
the expiration date is before today then
it it shows up in red if it's between
today and 30 days out it's yellow and if
it's beyond 30 days out it's green now
the cool thing about this is because we
have the today function built into the
conditional format this will adjust to
whatever day it happens to be relative
to your expiration date so you don't
have to put it put a fixed date in there
the today function will always
incorporate today's date according to
your computer into that now I think a
more elegant way to do this is to use
conditional formatting feature called
icon sets let's do the same thing but
well let's do with icon sets right here
in this range so if we go up to
conditional formatting and we'll drop
down here to icon sets and we'll pick
this green yellow red icon set right
here now
what we'll do is having put the icon set
it's not what we need yet we need to
adjust it so we're gonna go into
conditional formatting with this range
highlighted and we're gonna drop down
here to where it says manage rules the
only rule we have in place for that
particular set of cells is icon set rule
so I'm gonna select it and then I'm
going to click Edit now right over here
this is the typical boilerplate which
really has no Apple
occasion of what we're trying to do here
with icon set so we're immediately going
to change the parameters to formula
based parameters for both of these
conditions now with a three icon set
rule all you have to do is set two of
the parameters and the third one is
implied so the green parameter is going
to be equals today plus 30 and then the
bottom parameter is going to simply be
equals today and let me interpret that
for you so what that will say is the
yellow condition will be anything
between today and today plus 30 and the
red condition will it be anything less
than today so you don't have to create
that third parameter it's implied by
putting those two parameters in there so
here's how you do it with icon sets then
check it out we're going to go ahead and
click OK and then ok and here you go so
you decide which one you like better
this is the traditional way of doing
conditional formatting what she'll see
with cell shading and fill and that kind
of thing and this is the icon driven way
so there you go play this over try it
you can put it in an entire column where
you have dates where you want to have
your attention focused on dates
particular items that may have been
expired or getting close to expiration
this can be used in project planning it
can be used when you have different
types of things that in the food
services industry where you want to be
tracking expiration dates in all sorts
of places now if you have any questions
at all about this please feel free to
give me a call or contact me my name is
Rob Hamilton we're from Sol Canyon
training and development also I'd like
to just let you generally be aware that
we have a wonderful program out there
called Excel essentials that has over 50
tutorials where you can basically learn
excel at your own speed if you'd like to
learn more about that go onto our
website wwlp.com click on over to
products will tell you all about it once
again if you have any questions at all
let me know so I wanted to share this
one you with you I get this question all
the time and I'm finally putting out a
tip that gives you an easy way to track
expiration dates and due dates boom
Shakalaka
[Music]
you
[Music]
