NETWORKDAYS Function | Calculating Net Work Days in Power Query | Weirdness & Warnings

Santosh asked about NETWORKDAYS and calculating net work days in Power Query. OUCH! This was rough.

NETWORKDAYS is a weird function. If you work at a restaurant that's open 7 days/wk or a salon that's closed on Mondays and Tuesdays, NETWORKDAYS makes a mess. However, using Power Query can be complicated with a lot of steps, but it's more accurate than fiddling around with NETWORKDAYS.

This video has several phases:
0:00 Introduction
2:22 The NETWORKDAYS function
4:46 Calculating Net Work Days in Power Query
12:40 Outro

Download the file:

#NETWORKDAYS
#POWERQUERY
#Anti-Join
For a list of my Excel courses at Lynda/LinkedIn:


There are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.


My book: Guerrilla Data Analysis 2nd Edition

[mchllwoods]

I'm sorry but there r holes in your story. How can u drive all the way to India??? U need a plane to get there. Also, are we still on lockdown? We can't travel over seas right?!

[Giridharan]

Again hitting upon the deep meaning of EXCEL ON

[abdulrazakabdulkarim]

YoWatchimg your videos since January 2019, u changed my life, I went from zero to hero. Thank you so much

[deepakbisht]

You nailed it OZ. Uncle oz becomes networkdays WALA. Thank you so much for showing us the PQ solution.

[gulshanmehta]

Hats off Sir. You are really explained in a very cool manner. You are a Networkday, "WALA".
😀😀😀

[ramkrishna]

This is wonderful. I was not aware of the NETWORKDAYS function. But after seeing your approach I am already thinking of ways of using it in my work where I deal with dates and holidays. Thank you, Oz for this wonderful video :)

[coogs]

Boooffffhhgh, wa laaa..just like that Oz..the magic man...love your videos, keep up the great work sir..

[ExcelExciting]

Oz Ji !! Super Duper Like 👍.. you should have taken a stop in Saudi Arabia while driving back home 🏡. "शानदार वीडियो"

[wayneedmondson]

Awesome Power Query date sorcery! Thanks for sharing this wild ride :)) Thumbs up!!

[tonyful]

Incredible... Very interesting... Thank you so much oz...

[ivanmamchych]

Very interesting and useful video-lesson, as usually. Thank you, Sir

[santoshkamala]

You nailed it Oz. Awesome explanation !!!

[saptarshidas]

Thank you Networkdays wala sir, awesome video ❤💯🙏🏻👌👍

[johnhackwood]

As entertaining as that was it also was such a display of how to use PQ for a real world practical problem that the native formula just isn't going to do.
Look at how more configurable this PQ solution is for the real world - e.g. 3 non working days a week off not 2 or say a 2 month shutdown Jun/July and then 2 non working days a week but 3 on the last week of the month. Thanks again Oz!

[ramanhai]

Great. ❤️❤️❤️❤️ Can I found a solution for overtime working hours in addition to this.

[madhun]

That was so useful… I have a question, please help
Scenario is We have tasks which need to be completed with in the scheduled timelines however employees take extra days to finish the task.. existing excel has 3 dates …start time, end time and actual end date. In actual end date employee updates the date they completed the task. I need to get duration of days and if they have met or not met the deadline and how many days they took

[aman_mashetty]

Got a problem OZ(network wala) has a solution. Thanks for another great video

[CeliaAlvesSolveExcel]

Great and fun challenge, Oz! I loved how you invoked that function. Thank you for showing that.
I think the dates changed on you because you didn't set up the column as date type on PQ before loading into Excel.

[timmybone]

I thought I was the only person that celebrates Giraffe Day every year. Thanks for another entertaining video Oz!

[excel-in-g]

I'm (again) challenged here, Oz. Got to give it go.