Skip to content

Email Newsletter Challenge 10

This week’s challenge will test your ability to solve a common problem. I also threw in a bonus question. Think you’re up to the challenge?

Download the spreadsheet challenge using the links below.

The Challenge

This time, your challenge is calculate the number of workdays (Mon-Fri) between the two dates excluding the listed holidays. Your bonus question is to perform the same calculation, but exclude all Fridays as well.

The Solution

The solution is the same for both Excel and Google Sheets.

You will use the NETWORKDAYS function to calculate the number of workdays (Mon-Fri) between the two dates excluding the listed holidays.

Select cell E4, and enter the following formula.

=NETWORKDAYS(C3, C4, C7:C13)

The NETWORKDAYS function accepts three arguments.

  1. The start date
  2. The end date
  3. A range containing the dates to be excluded from the count (this is where the holidays go)

Finally, the NETWORKDAYS function calculates the number of workdays between the start and end date (including the start and end date), and it excludes all dates from the range used for the third argument.

Solution – Bonus Question

The solution is the same for both Excel and Google Sheets.

You will use the NETWORKDAYS.INTL function this time. This function works the same way as the NETWORKDAYS function with one exception. This function allows you to specify the days that count as the “weekend.”

Select cell E7, and enter the following formula.

=NETWORKDAYS.INTL(C3, C4, "0000111", C7:C13)

The NETWORKDAYS.INTL function accepts four arguments.

  1. The start date
  2. The end date
  3. The weekend
  4. Holidays

The first, second, and last arguments are the same as before. The difference this time is the third argument.

This argument can be a number code from 1 to 17, or it can be a text string like the one used in this example. The different number codes represent different “weekends” such as Sunday and Monday, or just Sundays.

However, in our case, we want to consider Saturday, Sunday, and Friday as the weekend. There is no number code that represents this type of weekend. So we have to use the string method instead.

Using the string method, weekends can be specified using seven 0’s and 1’s, where the first number in the set represents Monday and the last number represents Sunday. A zero means that the day is a work day, a 1 means that the day is a weekend. For example, “0000011” would mean Saturday and Sunday are weekends.

In our specific example, “0000111” means that Monday through Thursday are workdays and Friday through Sunday are weekends.

As a result, our formula calculates the number of workdays between the start date and end date while only counting Monday through Thursday as workdays excluding holidays.

For more information on the NETWORKDAYS.INTL function, you can check out the official documentation articles below.

Thanks for challenging yourself this week, and I hope you learned something new.

And if you’re not signed up for my weekly email newsletter, you can sign up right here!

This Post Has 0 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top