Note: Because this is an ongoing series, viewers will not receive a certificate of completion.
Skill Level Appropriate for all
- [Instructor] When you're working with data that involves different dates, you might want to note day of the week. And in this worksheet here, we've got a day for every single day of the year, starting right here, and by the way, to create this list, it's pretty fast. You can simply start by typing in, for example, the first day of the year, if that's where you're beginning, and as soon as you drag the lower right-hand corner downward, you'd have to do this down to row 365, 66, whatever, but keep in mind, when you do drag the date entry, automatically, you get next day, next day, next day, automatically. So, creating the date in column A is pretty easy.
But sometimes we want to know day of the week. Now, it remains to be seen what we might be doing with these other columns, but if we do want to know which day of the week these dates are, we simply use a function called weekday. What's the weekday of January first? You might remember, earlier in the year, this was a Sunday, but when we use this function, the answer comes up as one. Now, if I drag this downward just a few cells, I think you'd reach the conclusion pretty quickly, one means Sunday, two means Monday.
You might have to pull our your calendar, at least initially, to see this, but eventually, you begin to see what's happening. So this is valuable information. We probably wouldn't leave it looking like this, although we could. I think what most people would prefer to see here much of the time would be the actual day of the week. And we can do this rather easily, simply by pressing Control + 1, which activates the Format Cells dialog box, and you need to go to the number tab, unless it's already selected. Choose Custom, and then in this panel right under the word Type, usually you'll see the word General in there.
If you put in three d's, right away above this, you'll see S-U-N, Sunday. So if you want three-letter abbreviations, and I'll use this first, we'll simply click OK. I'll double-click here. We have that look. If you prefer full spellings, we'd use four d's. So once again, Control + 1 gets us here quickly, back to Custom. How about four d's to spell out the day of the week. And we do this all the way down the columns, so at the end here, I'll just double-click to copy this down the column. So the weekday function is being used in all of these, all the way down to the bottom as I double-click the bottom edge of one of these cells.
So there it is for the entire year. So that's useful information. It just depends on what you want to do with it from there. Now, it's possible that we might want to highlight just the weekend days or just the weekdays in a certain way. So what if we say, let's just highlight these? We don't necessarily need to have this data here. If the objective all along was simply to highlight the weekend days, we can use conditional formatting along with that weekday function, and although typically, you don't need to prepare the formula outside of conditional formatting, I'm going to do this ahead of time.
So here's what we're about to do, and I'll show you the formula we will be using. We want to apply a format in column A by way of conditional formatting that, in effect, says, if the weekday of any given cell here happens to be one or seven, which means Sunday or Saturday, then let's make that cell have a different color or any other different formatting feature that we might want to apply. So the function's going to look something like this. We want to use a function called or along with weekday. As you're typing function names, you don't need to capitalize them, and we'll use in our example cell A1, because when we select column A, that will be the active cell.
If the weekday of that is equal to one, or if it happens to be equal to seven. So I'll just highlight this data right here, press Control + C, and paste it over here with Control + V and change that to a seven. So if either of these happen to be true, that's the formula we'll use. Now, I'm entering that right now. Of course, that's looking at cell A1 and doesn't mean much. So I'm going to leave this here, just for reference purposes. I'll put a space in front of it, but I'll highlight just this portion of it, press Control + C to copy, leave that space there, Enter.
Now I will apply conditional formatting in column A using that formula. Conditional formatting, a new rule. It doesn't fit any of the standard rules. Use a formula to determine which cells to format. I'll click on the panel right here, press Control + V to paste that, and there it is. That does need a leading equal sign, too. There we are. Let's apply a format. The most obvious format would be a cell color background. If you don't like any of these colors, we can go to More Colors, pick one out of here. Maybe a bright green, something like that.
Click OK, click OK, and OK, and just the weekend cells, you can see pretty readily. We don't necessarily need to have column B there anymore, but we can see readily here. Now, maybe this was just a simple list where we want to put comments out here or other columns, but we know at a glance where those weekend days are. And since I did that for the entire column, if I go to the bottom here, you'll notice down here, you'll see it going on indefinitely. That doesn't necessarily cause any problems, but if I start dragging right down here from the corner, see what's happening? We're dragging to 2018, but that green color is going to pop up in the appropriate locations where it is.
Now, ultimately what we could do is remove conditional formatting from the lower cells, but it doesn't really cause any problems. So we could leave it that way. We want to zip to the top, click on a cell, double-click its top edge. Now, on this other worksheet here called Transactions, we've got a whole series of transactions going way back into 2010 and all the way into 2017, near mid-year. Lots of different transactions here. They're sorted by date. Here, too. Some of these might have occurred on weekends. We want to know that.
In this example here, we want to use that same kind of formula, but for all three of these cells. In order words, if this were a weekend date, we want these three cells to be highlighted. So that formula I used here is almost the one we want. In fact, I'll highlight that, press Control + C and escape, and jump over to this other worksheet and select these three columns. Once again using conditional formatting, we create a new rule. Use a formula to determine which cells to format.
Click in here, equal, and in the example here, because we're using data in different columns here that we also want to apply the color to, we need to change the formula slightly. We're always looking in column B here for the date, and not just a simple B, but a dollar sign in front of it, and I'll display this larger. And the same thing over here. We're looking at B1, even though, for the moment, we know B1 doesn't have an actual date in it, but as we move up and down the column, we're always looking in the column B.
So that might be a little odd, as you look at that $B. So in any example where column B contains a weekday that's one or seven, meaning Sunday or Saturday, we want that to be highlighted. So I'm going to highlight this, press Control + C, and actually paste it on the worksheet, 'cause it's slightly different than the other formula. Let's apply a format here. I'll just use a bright yellow. Click OK, and click OK, and there, we see what's happening. So that's a Saturday or Sunday.
We're not sure which one, necessarily, and we see the other weekend days as well. The formula in use here is this. I press Control + V. And then the reason the dollar sign is there is that one by one, and if we consider any single cell, and whether it should be highlighted or not, we look into the column B reference to see if that happens to be one of those weekend days. So that's the formula that's being used in the conditional formatting for the columns A, B, and C in the example here. Another situation where we might use a weekday could be in the list in column J.
We want to make sure that the shipping dates here are not Sundays. Let's say we don't do shipping on Sundays. And let's also put in a restriction that maybe these shipping dates have to be more than two days after the order date. So in column K, we're going to use a feature called, on the Data tab, Data Validation. And what we allow here isn't obvious at first. You might think it's somehow going to be related to date. Well, it is, but we don't use this option. We go to Custom, and the formula there might strike you as a little strange.
=K1, even though that doesn't have any data in it. At the moment, since I selected column K, K1 is the active cell. So we'll use this in the formula. =K1>J1+2, if we want to make sure this is always more than two days later. Let's suppose for the moment that maybe we're not thinking about the Saturday and Sunday aspect of this. So we simply say that the shipping date must be more than two days later. We'll click OK.
This kind of throws us a little bit, but we do want to continue. It simply means that K1 and J1 don't fit the argument properly. Yes. But if I put in the 17th here, it's not going to work, because it's not more than two days later. I'll retry and put in the 18th. That does work. But what day of the week is that? Let's use our weekday function. 1, it's a Sunday. So, let's keep that out of there. So what can we do? Let's, again, although we don't need to do it here, I'm going to prepare the formula, the data validation formula here.
I'll zoom in first. And then here's the formula we'll need. We need to make sure that two things are true. This time, it's equal and we need to make sure that the K1 entry is greater than the J1 entry plus two, just as before, but we also want to make sure that the weekday of K1 is not equal to, that's how we say not equal to, one, meaning Sunday. Now, as I press Enter here, the answer's not really relevant to us right now, but I'll put a space in front of that.
We do want this to be the formula to be copied. That's going to be the data validation formula. Again, using weekday, Control + C to copy that, and then I'll click column K as before, back to the Data tab, Data Validation, and that formula, I'll paste in here with Control + V. Once again, we're going to be getting a warning here. It evaluates to an error. But, and notice that data validation, unlike some features, doesn't work with existing data. That is a Sunday, but let's try the 25th now, which is also a Sunday.
And that is not accepted. Obviously, it's more than two days later, so it's okay there, but that doesn't work. We could go back and make that be the 19th, and later, we could not come back and make that be the 18th, because that's a Sunday. So we've seen different ways to use the weekday function to determine which day of the week, and whether it is that you want to highlight weekends or weekdays, we certainly have the tools available by using this function in a variety of ways. Here, we used it with conditional formatting. Here, we used it with data validation.
And there's certainly other uses of this function as we work with data in Excel, differentiating between weekdays and weekends as we wish.
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.