Sometimes, we want to highlight rows of weekends. In such a case, we can use conditional formatting but, first of all, we have to create a formula which will return TRUE for Saturday and Sunday, i.e. weekend days. We can start with the WEEKDAY function which will return the number of days in a week (Fig.1).
=WEEKDAY(A2)
We have our results. However, this numeration assigns number 7 to Saturday, and number 1 to Sunday. It’s not proper from our perspective. We have to modify it by changing the week day number sequence. The best option we can choose is number 1 for Mondays and number 7 for Saturdays. That’s why we have to write 2 in the second WEEKDAY function argument (Fig.2).
=WEEKDAY(A2,2)
And now, Saturday is 6 and Sunday is 7. We can clearly see that weekends are numbers bigger that 5 (Fig. 3).
Now, we can simply create a logical test that defines if the week day number is greater than 5 (Fig. 4).
=WEEKDAY(A2,2)>5
When we copy our formula down, we can see that we have TRUE for Saturday and Sunday, and FALSE for the rest of the days (Fig 5).
The results are proper, but we have to remember that we want to highlight the whole row. We have to ask ourselves how our reference should behave. We know that we will be copying the formula down and to the right (horizontally). What we have to remember is that we always want to look at column A, which is the Date column. Even if we want to go one or two columns to the right, we always want to look at the cell from column A (Fig. 6).
That’s why, we have to lock the column by pressing the F4 key, but not the rows. It means that we have to put only one dollar sign (Fig. 7).
=WEEKDAY($A2,2)>5
When we copy the formula down, nothing changes. But, when we copy it to the right and down, we can see that TRUEs and FALSEs are in a row (Fig. 8).
Now, we have to copy our formula and the select the range on which we want to add conditional formatting. The range starts with cell A2, so let’s press this cell, then Shift + →, two times to the right and Ctrl + Shift + ↓ to the end of our data. Cell A2 has to stay active and we will start creating our formula from the perspective of this cell (Fig. 10).
Now, we go to the Home tab, then to Conditional Formatting, and we select the New Rule option (Fig. 10).
In the window that will appear, we have to select the Use a formula to determine which cells to format bar, then paste our formula into the Edit the Rule Description and press the Format button (Fig. 11).
In the Format Cells window, we press the Fill button, then choose the color we like. Let’s choose a shade of grey, then the OK button, and the OK button once again in the New Format Rule window (Fig. 12).
Now, we can see that each day of the weekend is highlighted the way we wanted (Fig. 13).
