There is no NETWORKDAYS() Function in PowerPivot
A Post on Thanksgiving?
Normally I would take today off and not have a post. But I’ve posted so many updates about the book lately that it’s got to feel like this place has turned into an advertisement shop, and I want to keep the “real” content up.
So consider this a Thanksgiving “bonus” post – me giving thanks for everyone ordering the book, and everyone tolerating my desire to post updates about “my baby” every five minutes
A Missing Function
It’s funny, I’ve never used NETWORKDAYS() much (if at all) in regular Excel, so I didn’t realize this until someone posted on the MrExcel forums – how do I do a NETWORKDAYS()-style calculated column?
Something like this:
So how do we get to this?
Now let’s add a column to that table that is 1 for workdays and 0 for non-workdays:
Hey, that one returns 0 for Saturdays and Sundays, which is true for the United States. When I visited Dany Hoter in Israel this summer, I learned that Fridays and Saturdays are the weekend. So adjust accordingly
Furthermore, if you have a more sophisticated Calendar/Dates table that includes columns like IsHoliday, feel free to use that instead. I’m just using the simplest flavor here.
Now for the Calculation
Back in our original table (which is named TwoDates), here is the final formula:
It just sums the IsWorkday column from the Dates table, using the two columns in the current table (TwoDates) as the endpoint to DATESBETWEEN().
That’s right, there is NO relationship between these tables. We are not actually using the Dates table to filter the TwoDates table in this scenario, which is rare – Dates tables are almost ALWAYS related to the data tables. So this is an exception to the normal rule for sure.
Actually we’re using the TwoDates table to filter the Dates table, in a way, but we’re doing that in our formula logic using DATESBETWEEN() and do not require a relationship. It would be hard to create a relationship here even if we wanted one though – which column in TwoDates would we use, Column1 or Column2? And even then the filters would flow from Dates to TwoDates, which is the reverse of the filtering we are doing here.
So again, chalk this one up as a major exception to the normal rule