- All right, if
you're using Google Sheets and you have list of items
that have different dates in the future when
they're going to expire, maybe when they're due,
now we're going to go over several different ways to highlight those to give you a visual
illustration of what's coming up. So the first thing that we'll do, we'll come over to this food inventory and let's just say you want
a general idea, all right? So this general idea is
actually going to be relative and it's going to give
a gradual highlighting on these based on how old they are. So we'll go to Format and
Conditional formatting and I've actually already clicked that that's why that's up
on the right over here and I will left click on Add another rule. And since I've already selected the data that I want to highlight, it prefilled in this apply to range, right now, it defaults to formatting
it if it's not empty. All right, so that's why it's all green because there's dates and everything. But first we're going
to go to Color scale, we'll come back to Single color in some of these other
examples, and again, we just want to get a general idea of when these items are going to expire and one of the nice ways to do it is just to go from a
red to a green, right? So if I do that and I come back over, what that's done is the
dates that are expiring soon are in red, so it looks like
you need to address them. In other words, to you need to drink the orange juice here, right? And eat the tomatoes and the items that are in green are much further away. One thing to watch out
for and enlist like this is if you have an outlier is
going to reduce the contrast of everything else, so you
only have one dark green and then some light green
items in here and it looks like this January 4th, 2022,
is right in the middle. Well, if you bring this date down, so instead of 2037, let's make it 2027, you're going to see more color
come into the other items, right, and that'll help you
see it a little bit better, hopefully you're starting
things in the next five years so, it doesn't really matter
that you change the date but that's a quick and easy way to do it. You don't have to do any formulas and it will show you
what's new and what's old. All right, so let's come
over to the next example. I've just made a simple list,
let's just say these are bills that you have coming through, right? And you want to know
everything that's due before, let's say June 12th, all right, so you have your checkbook out or you're logged in your bank account, you're ready to pay everything, really you should have these
on automatic bill payment, that's another story. So we'll highlight these
dates, come back over to conditional formatting
and we'll do a single color, and we want to format the cells if and we'll left click on this dropdown and there's a lot of useful
options here, in this case, we're going to say if the date is before and we will do a exact date
and we'll say June 12th, 2021. All right so it highlighted
these three in green and that's nice and easy, right? So everything before a
given date is pretty simple, you just use date is before
and use an exact date. This one we'll do and yellow, all right, make it look a little bit different than the last one that we did, we'll left click on Done. Come over to the right, all right, so we'll do a couple different
things in this example but the first thing that we're going to do is kind of straight
forward, we'll highlight the dates again, we'll left
click on Add another rule and we wanna highlight everything, X number of dates after today, so it's going in a little
bit different direction where we're going to say greater than because we want to use a number of dates. We could say date is
after but then you'll have to kinda manually count out
in your head a certain date, so we'll say greater than and then you just get an
open formula box here. So like all formulas, you
start it with an equal sign and then the value of today, we're going to make that relative so when you open this up tomorrow, it adjusts forward by one, so we'll use the formula called today and it just needs empty parentheses in it because all
formulas need parentheses but this doesn't take any inputs today, just returns today no matter what, but then we're going to
say seven days after today. All right, so today plus seven,
this will always be one week in the future. Left click on Done, see
what this looks like, it looks nice, right? Let's slide over to the
right a little bit more. Well, one thing about we could do though is you're really interested in which books that you can hold onto for a while, you have some more time to read, let's shift the highlighting from the dates over to
the books, all right, because the actual names of the books are what you're interested in. This is a good book by the way, I don't know how popular
it is, but I read it, I liked it, it's kinda tragic, but, could probably tell that by the title, let's take this highlighting
off of the dates, actually, let's be
smart and copy at first. Right, so we will hover over it, we will left click on remove rule and we will highlight just
the range that has the name of the book, so we will
want to highlight it based on these dates,
but you need to select the actual range that
you're going to apply the highlighting to. And so when we left click
on add another rule, apply to ranges is right, format cells if, this is where you come down
and do a custom formula. All right, so now you
can do whatever you want and we will say equals and when
we're writing this formula, we want to write it as if
we're in this top cell, so you see the little
blue rectangle around "To Kill a Mockingbird," write the formula as if you were only in that and then it will evaluate
every subsequent cell and it will shift the formula down just like it would in the spreadsheet. All right, so we will say,
oh, let's paste that in here. You don't want two equal signs though, all right, so we don't have
the whole formula done yet because nothing in that
range is equal to seven days from today, right? And we wanna actually
be greater than today but we'll get there. And what we want to do,
if we're in cell I10 we wanna look at J10. J10 is equal today plus seven but we also wanna make
that a greater than sign instead of equals and there we go, that's working again. So, in the cell I10, we're looking to see if J10 is more than seven days out. All right, so we'll click Done, that list is done, and
we'll go to one more type of formatting here by
expiration date or due date and what we want to do with
this is we want to make a couple of different buckets if you
will, or different bands. And we wanna look at date
ranges that are 30 days out and date ranges that are 60 days out. So let's just say you're working in the accounting department, you want to pay these vendors, the vendors that are 60 days out, you definitely want to pay
'cause you're late on those and the vendors that are 30 days out, you probably wanna pay
because they probably have terms of 30 days, but some of those you may wanna hold on to so
it's just a different type of action based on the date. So let's highlight the due dates here, we could highlight the
vendor names, right, by looking at this column,
but we'll keep this simple, so we're talking about
one concept at a time. We'll do Format, Conditional formatting and the first range that we want to do is greater than 30, less than 60. All right, so the formatting
rules, let's drop this down and they have a great option
here that says is between. And we want it to be between today plus 30 and today plus 60. Right, so that's everything that's between 30 and 60 days old,
we'll make that yellow, we will click Done, we'll add another rule and we will say this one is
after or greater than today plus, we'll say 61, I think
the other one will capture 60 and we'll make this one
red, that one's really old, you need to Peach Tree,
that, and we'll do one more rule just to make this look nice. We'll add another rule
and we will say this is less than today plus 30
and we'll click Done. All right, so we have current bills, we have past due bills,
and then we have the bills that you definitely need to
pay or you might frustrate your vendor. All right, and we want
to have exact control over how these numbers
look, so in this example, I don't really care too much about cents but I want to show that
there are currency, in this next video, we'll
go over how to control the way in which your numbers show up and how to have granular
custom control over that if you want it, I'll see
you in that next video, thanks. (gentle upbeat music)
