Excel NETWORKDAYS part 3: calculating custom weekends with Power Query

In the previous 2 videos we’ve looked at calculating NETWORKDAYS and days off that aren’t just Saturday and Sunday. Thanks for Bill Szysz, Wayne Edmondson, XLarium and others for leading a lively discussion and offering really cool formula-based solutions. (The conversation is on this video: https://youtu.be/njC1AvEU3fg)

In this video I show you something I learned from Wayne and Bill. You can create your custom weekends by putting together a string of 1s and 0s that represent each weekday.

1 = day off
0 = workday
The string starts with Monday.

If you work (or go to school) only on Tuesdays and Wednesdays, your string would look like this:
“1010111”
Thus:

=NETWORKDAYS.INTL(Start_Date, End_Date, “1010111”, [Holidays])

You’ll also see Power Query, replace values and merge columns in order to simplify calculating net work days in Excel.

For a list of my Excel courses at Lynda/LinkedIn:
https://www.linkedin.com/learning/instructors/oz-du-soleil
There are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2