Conditional formatting based on date proximity

- [Instructor] In this worksheet we've got a list of orders and dates. And in column B we want those dates to stand out based on what we see in the green panel to the right. If today's date is in there and at the time of this recording it's June 19th, you might have already seen that in cell B5, we want it to be red. If it's a date within a week of today on either side we want it to be yellow. And if it's a day within two weeks we want it to be green. And we can do this by a way of Conditional Formatting. The formulas, although not terribly difficult, might be a little bit challenging, at least at first.

And behind this green panel, I'm just gonna click and move it downward, are the formulas in question. And you don't wanna watch me type these, but I wanna show you them ahead of time. Here are the three formulas we actually want to use. The first one on top here is this. Now you might be questioning why does it say B1? I'm gonna be selecting column B before I use Conditional Formatting and as I do that that makes B1 become the active cell. When you write Conditional Formatting formulas you'll often use a single cell and yet by inference all the highlighted data one-by-one is evaluated in the same way.

And that might be a little bit surprising, but that's the way it works. So the way we apply these rules, the order in which we apply these sometimes is relevant, but we can change the order. So let's say we do what we would want to do first, apply the red. So I'm clicking column B, going to Conditional Formatting, and none of the standard uses of Conditional Formatting fall into place here. But we do have the ability to establish a New Rule, that's what we're about to do here. Use a formula to determine which cells to format. So I'll be using that first formula, and I didn't copy this, 'cause it's relatively short.

=B1=TODAY. You don't have to capitalize these, but it'll easier to read. The TODAY function always is followed, like all functions are, by parentheses, but there's never anything between them when we use TODAY. So =B1=TODAY, open and close parentheses, Format, any format we want. In this case Fill, red background. Click OK and OK and there you see it happening. There could be others in the list down below, doesn't look like there are any, but that could certainly happen.

Now for yellow and green, longer formulas. Two things need to be true. Let's just focus on this one for the moment. We want cells to be yellow if the current date, whatever that is, happens to be greater than today's date minus seven and at the same time it's less than today's date plus seven. And working this out, undoubtedly if you're playing with these formulas, a little bit of false starts here and there, we eventually get it right. But that's the one we're gonna be using right here. So here too, I'll simply highlight that, press Control + C and Escape, go back to column B, Conditional Formatting, New Rule, Use a formula to determine which cells to format, click in the panel here, and I'll press Control + V to paste.

And the Format here, yellow background. And OK. And OK, and we see what's happening. Now that made the current date yellow. The reason it is is because each subsequent rule overrides the previous one. So we can come back and change the order of these. I wanna do the other formula first, it's green. I can just copy this or use the previous one and simply change the seven to a 14, either way. So highlight this, press Control + C, Escape.

Again, selecting column B, going back to Conditional Formatting, New Rule, Use a formula to determine which cells to format, click in here, Control + V to paste. This time the Format, this is the 14 day difference on either side. We want this to be green. Maybe this green will work or maybe we go to More Colors, pick a lighter green perhaps, maybe that'll work. Click OK, click OK, and OK. We've got some greens in there, but notice how the yellows have been wiped out.

So what do we do in a situation like this? Back to Conditional Formatting, we manage the rules. And we wanna change the order of these. We don't have to move them up and down by dragging them or anything, but simply, for example, click the last one here, the red, we actually want that to be the dominate one. We'll click the up arrow here twice. And we want the yellow one to be more important than the green one, so we'll click there and move it up. And then click OK and now I've got some numbers that make sense. Now if you try this at a later date using these same dates, of course, you're gonna get different coloring schemes and you can change these if you wanted to manipulate them with a random function or something like that working with other dates.

But you could imagine how powerful this can be to highlight certain bits of information based on date entries using Conditional Formatting.