Join Dennis Taylor for an in-depth discussion in this video Conditional formatting based on date proximity, part of Excel Tips Weekly.
- [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.
Author
Updated
3/2/2021Released
1/16/2015Note: Because this is an ongoing series, viewers will not receive a certificate of completion.
Skill Level Intermediate
Duration
Views
Q: Why can't I earn a Certificate of Completion for this course?
A: We publish a new tutorial or tutorials for this course on a regular basis. We are unable to offer a Certificate of Completion because it is an ever-evolving course that is not designed to be completed. Check back often for new movies.
Related Courses
-
Excel: PivotTables for Beginners
with Dave Ludwig23m 52s Beginner -
Presentation Tips Weekly
with Jole Simmons3h 32m Intermediate -
Excel: Lookup Functions in Depth
with Dennis Taylor1h 8m Intermediate
-
Excel Tips - New This Week
-
Previous Episodes
-
Quick formatting tips8m 9s
-
Create an organization chart8m 56s
-
Auditing9m 1s
-
Adding comments and shapes7m 35s
-
Creating an Excel template7m 57s
-
Adjust banded-row formatting14m 35s
-
Dealing with circular errors8m 20s
-
Sorting tips and shortcuts8m 40s
-
Freeze Panes and Split5m 30s
-
Calculate % of change5m 41s
-
How to adjust names5m 45s
-
Sorting by moving columns4m 16s
-
Using the Solver Add-in4m 29s
-
Create picture links5m 37s
-
Display large values5m 16s
-
Create a powerful macro4m 40s
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.
CancelTake notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.
Share this video
Embed this video
Video: Conditional formatting based on date proximity