There is no NETWORKDAYS() Function in PowerPivot

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 Smile

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:

NETWORKDAYS in PowerPivot

Desired Result

So how do we get to this?

Date Table!

imageAs is often the case when you’re dealing with dates in PowerPivot, you need a Date/Calendar table first, so let’s create a single column Dates table, pictured here at right.

IsWorkday Column

Now let’s add a column to that table that is 1 for workdays and 0 for non-workdays:

=SWITCH(WEEKDAY([Date]),1,0,7,0,1)

Variations

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 Smile

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:

NETWORKDAYS in PowerPivot:  =CALCULATE(SUM(Dates[IsWorkday]), DATESBETWEEN(Dates[Date], TwoDates[Column1], TwoDates[Column2]))

=CALCULATE(SUM(Dates[IsWorkday]),
           DATESBETWEEN(Dates[Date],
                        TwoDates[Column1],
                        TwoDates[Column2]
                       )
          )

It just sums the IsWorkday column from the Dates table, using the two columns in the current table (TwoDates) as the endpoint to DATESBETWEEN().

No Relationship?

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 Smile

Download the Workbook

Click here to download the workbook