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).
Highlight weekends with conditional formatting
=WEEKDAY(A2)
Fig. 1 WEEKDAY function
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)
Fig. 2 Selecting the correct option
And now, Saturday is 6 and Sunday is 7. We can clearly see that weekends are numbers bigger that 5 (Fig. 3).
Fig. 3 Weekend bigger that 5
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
Fig. 4 A simple logical test
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).
Fig. 5 TRUE and FALSE
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).
Fig. 6 A cell from column A
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
Fig. 7 Locking the column
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).
Fig. 8 Rows
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).
Fig. 9 Selected range with an active cell
Now, we go to the Home tab, then to Conditional Formatting, and we select the New Rule option (Fig. 10).
Fig. 10 New Rule option
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).
Fig. 11 New Formatting Rule window
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).
Fig. 12 Format Cells window
Now, we can see that each day of the weekend is highlighted the way we wanted (Fig. 13).
Sometimes, we want to highlight whole rows of holidays and weekends. In our case they will be the second, the third and the fourth of September, the sixth of September and so on, depending on how many holidays and weekends we have. We can highlight them with conditional formatting, but first of all, we have to create a proper formula. We are going to use the NETWORKDAYS function. We want to start with the date from the first row and finish with the same date. We also have to add holidays and lock it (Fig. 1).
=NETWORKDAYS(A2,A2,$G$2:$G$3)
If a given day is a working day, the function returns 1, and if it’s a day off, it returns 0 (Fig. 2).
Fig. 2 Working days and days off
Now, we want to highlight the rows, where we have 0. We can do it by creating a proper logical test. We have to check whether a value returned by the NETWORKSDAYS function is equal to 0 (Fig. 3).
=NETWORKDAYS(A2,A2,$G$2:$G$3)=0
Fig. 3 A simple logical test
Now, we want to ask ourselves how we want our reference to behave. Since we want to copy the formula down, we want to change rows, as there are different dates in each row. We also want to highlight a whole row, so we always want to look at the first cell in the row because it’s a date. Having this in mind, we have to lock the column by inserting a single dollar sign before the name of the column, but not before the row number. We must do this both in the start date and the end date argument (Fig. 4).
Fig. 4 Locked cell
After copying it down and to the right, we have proper results. We have TRUE for weekends and holidays, and FALSE for working days (Fig. 5).
Fig. 5 Proper results of TRUE and FALSE
Now, we can copy our formula in the edit mode and select the range in which we want to have conditional formatting. We click on the Shift + →, two times to the right, and Ctrl + Shift + ↓ to select the data to the end. While cell A2 is still active, we can go to Home tab, then Conditional Formatting, then the New Rule option (Fig. 6).
Fig. 6 New Rule option
In the New Rule Formatting window, we have to select the Use a Formula to determine which cells to format bar, then paste the formula into the Edit the Rule Description bar, and click on the Format tab (Fig. 7).
Fig. 7 New Formatting Rule window
In the Format Cells window, we select the Fill tab, then choose a nice color, then press the OK button in the window, then the OK button in the second window (Fig. 8).
Fig. 8 Format cells window
And there we have it. We can see that each row with a day off is highlighted in green (Fig. 9)
Fig. 9 Highlighted rows
Even if we modify the holidays, which are our criteria, our highlighted rows also change (Fig. 10).
