Highlight weekends and holidays with conditional formatting

Some­times, we want to high­light whole rows of hol­i­days and week­ends. In our case they will be the sec­ond, the third and the fourth of Sep­tem­ber, the sixth of Sep­tem­ber and so on, depend­ing on how many hol­i­days and week­ends we have. We can high­light them with con­di­tion­al for­mat­ting, but first of all, we have to cre­ate a prop­er for­mu­la. We are going to use the NETWORKDAYS func­tion. We want to start with the date from the first row and fin­ish with the same date. We also have to add hol­i­days and lock it (Fig. 1). 

=NETWORKDAYS(A2,A2,$G$2:$G$3)

If a giv­en day is a work­ing day, the func­tion returns 1, and if it’s a day off, it returns 0 (Fig. 2). 

Now, we want to high­light the rows, where we have 0. We can do it by cre­at­ing a prop­er log­i­cal test. We have to check whether a val­ue returned by the NETWORKSDAYS func­tion is equal to 0 (Fig. 3).

=NETWORKDAYS(A2,A2,$G$2:$G$3)=0

Now, we want to ask our­selves how we want our ref­er­ence to behave. Since we want to copy the for­mu­la down, we want to change rows, as there are dif­fer­ent dates in each row. We also want to high­light a whole row, so we always want to look at the first cell in the row because it’s a date. Hav­ing this in mind, we have to lock the col­umn by insert­ing a sin­gle dol­lar sign before the name of the col­umn, but not before the row num­ber. We must do this both in the start date and the end date argu­ment (Fig. 4).

After copy­ing it down and to the right, we have prop­er results. We have TRUE for week­ends and hol­i­days, and FALSE for work­ing days (Fig. 5). 

Now, we can copy our for­mu­la in the edit mode and select the range in which we want to have con­di­tion­al for­mat­ting. 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 Con­di­tion­al For­mat­ting, then the New Rule option (Fig. 6). 

In the New Rule For­mat­ting win­dow, we have to select the Use a For­mu­la to deter­mine which cells to for­mat bar, then paste the for­mu­la into the Edit the Rule Descrip­tion bar, and click on the For­mat tab (Fig. 7). 

In the For­mat Cells win­dow, we select the Fill tab, then choose a nice col­or, then press the OK but­ton in the win­dow, then the OK but­ton in the sec­ond win­dow (Fig. 8).

And there we have it. We can see that each row with a day off is high­light­ed in green (Fig. 9)

Even if we mod­i­fy the hol­i­days, which are our cri­te­ria, our high­light­ed rows also change (Fig. 10). 

https://www.youtube.com/watch?v=TY8sHfaVgaM