Sometimes, we want to count a certain number of weekdays between two dates. If we want to count workdays, we need to use the NETWORKDAYS function. But, if we want to count the number of, let’s say Fridays, we have to use the NETWORKDAYS.INTL function. We start with the start date, finish with the end day, and then we modify weekends. In the Excel help description, we can see that we can change working days and days off using a text string of 1s and 0s, where 1 means a day off and 0 means a working day. So, Monday, Tuesday, Wednesday, Thursday are 1, Friday is 0, Saturday and Sunday are 1 (Fig. 1).
Number of Fridays between two dates
=NETWORKDAYS.INTL(A2,B2,“1111011”)
Fig. 1 NETWORKDAYS.INTL function
Here, we have the number of Fridays between two dates. It’s important that the function also considers the start date and the end date in its calculations (Fig. 2).
Fig. 2 Number of Fridays
The NETWORKDAYS.INTL function can also work with holidays. Now, only Friday holidays are important for us, so let’s select the cell and press the F4 key to lock it (Fig. 4).
=NETWORKDAYS.INTL(A2,B2,1111011”,$F$3:$F#4)
Fig. 3 Holidays on Friday
And we have our results (Fig. 4).
Fig. 4 Results
Summing up, 0 means a working day, and 1 means a day off in the seven-number text string. If we write 0 also in the fourth place, it means that Thursday and Friday are working days (Fig. 5).
=NETWORKDAYS.INTL(A2,B2,1110011”,$F$3:$F#4)
Fig. 5 Two working days
As we can see, this small change modified our results once again (Fig. 6).
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).
When we want to count the number of working days between two dates we can use the NETWORKDAYS function. The function is very simple, as it needs only the start and end dates. And that’s it (Fig. 1).
Number Of Working Days Between Two Dates
=NETWORKDAYS(A2,B2)
Fig. 1 NETWORKDAYS function
And we have the numbers of working days between two dates. It’s important that the NETWORKDAYS function considers also the start and end days. If the start and end days are the same, the number will tell us whether the day is a working day or not (Fig. 2).
Fig. 2 Working days
We can also add holidays to this function in the third argument. We just have to select a range with proper dates and press the F4 key to lock it (Fig. 3).
=NETWORKDAYS(A2,B2,$F$2:$F$3)
Fig. 3 Holidays
Now, we can see that the number of days changed due to those holidays (Fig. 4).
Fig. 4 Changed numbers
In the NETWORKDAYS function, the weekend is considered as Saturday and Sunday. However, we can modify it by using the NETWORKDAYS.INTL function. The syntax of those two functions is almost the same. Apart from the start and end day, we can choose what our weekend days will be. Let’s choose Sunday only (Fig. 5).
Fig. 5 NETWORKDAYS.INTL function
Then, we can add holidays if we want. Let’s select the proper holiday range and press the F4 key to lock it (Fig. 6).
=NETWORKDAYS.INTL(A2,B2,11,$F$2:$F$3)
Fig. 6 Function with holidays
And we have our results. We can see that the number of working days between the dates grew because Saturday is a working day (Fig. 7).
