NETWORKDAYS() Equivalent in PowerPivot?

 
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

16 Responses to NETWORKDAYS() Equivalent in PowerPivot?

  1. David Hager says:

    Just for completeness:

    =IF(SWITCH(WEEKDAY([Date]),1,0,7,0,1)-RELATED(Holidays[HolidayDate])<1,0,1)

  2. Colin Banfield says:

    Hi Rob,

    Excel has a much richer environment for building a date table, as demonstrated in the 5-calendar date table that I created some time ago. The table contains a column for workdays in a month, using NETWORKDAYS and a lookup holiday table. In fact I use several lookup tables for various calculations. At the end though, you end up with a single flat date table.

    It never crossed my mind to attempt such a project in PowerPivot. I don’t see the point.

    • powerpivotpro says:

      The point of this post was really “what to do when you need a NETWORKDAYS-style calc column.” The separate calendar/date table wasn’t meant to be the focus.

      If you already have a good calendar table, you should use it – whether that comes from Excel or a db.

      But if you don’t have a date/calendar table yet, you’d need to make or find one, so I did the simplest thing I could in order to set up the second part of the post.

      And while that second part is a simple CALCULATE() formula, it’s important to remember that every day more people are brand-new to PowerPivot than the day before. (And those people might not yet have discovered the need nor the means to produce good calendar/date tables).

  3. David Churchward says:

    The “bad boys” of PowerPivot are out in the comments section today!

    I tend to agree with you Colin where a standard is evident, ie working days are the same throughout your dataset. I’ve used Rob’s approach where dates are a bit more variable. For instance, where an individual has a different working week to someone else, time sheet reporting etc. The resulting measure is a bit more complex than the one here, but the concept is very similar.

    Happy Thanksgiving guys!

  4. Colin Banfield says:

    David & Rob,

    You both make some very good points, and I should clarify a bit. I’ve never used NETWORKDAYS in practice without a holiday table, which would mean an extra table to create and use in PowerPivot. However, if you don’t need to consider holidays (even if they aren’t workdays), your solution is both clever and elegant.

  5. Kathy says:

    This worked Great!, but how can we expand it to include elapsed time between the dates with the hours in the work day included. The dates have a time element, we work 8am – 5pm. What is the Days, Hours, Min & Sec between the dates?

    • powerpivotpro says:

      Hi Kathy. OK, so you have a 9-hour workday. For starters you could just take the formula in this post and multiple it by 9. That will get you close in terms of hours, but not quite there.

      I think you need two more calc columns – one that calculates how many hours and mins were “left” in Column1, and one that calculates how far *into* the workday you were in Column2. Then you add those two amounts of hours and mins to your (workdays * 9) calc.

      EX: Column1 is 12:10 PM on some day and Column2 is 10:30 AM on another day. There are 4 hours and 50 mins “left” in that first day, and you were 1 hr and 30 mins “into” the second. Add those together and you get 5 hrs 80 mins, or 6 hrs 20 mins. Then you add (Workdays * 9) + 6 hrs 20 mins. But I would double check – this might give you one workday too many. You might need (Workdays – 1) * 9.

      • Bob Phillips says:

        I do this by extending the days formula to test if the start date is a weekday and subtract one, ditto the end date, then calculate the hours of the start and end dates and add these in, all in the single formula

        =(
        CALCULATE(
        SUM(Dates[IsWorkday]),
        DATESBETWEEN(
        Dates[Date],
        TwoDates[Column1],
        TwoDates[Column2]
        )
        )
        )
        – ( WEEKDAY( TwoDates[Column1], 2) < 6 )*1
        – ( WEEKDAY( TwoDates[Column2], 2) 5,0,”17:00:00″*1-MOD( TwoDates[Column1], 1 ) )
        +IF( WEEKDAY( TwoDates[Column2], 2 ) > 5,0,MOD( TwoDates[Column2], 1 )-“08:00:00″*1)

        • Bob Phillips says:

          That didn’t quite come out correctly
          =(
          CALCULATE(
          SUM(Dates[IsWorkday]),
          DATESBETWEEN(
          Dates[Date],
          TwoDates[Column1],
          TwoDates[Column2]
          )
          )
          )
          – ( WEEKDAY( TwoDates[Column1], 2) < 6 )*1
          – ( WEEKDAY( TwoDates[Column2], 2) 5, 0, “17:00:00″*1 – MOD( TwoDates[Column1], 1 ) )
          +IF( WEEKDAY( TwoDates[Column2], 2 ) > 5, 0, MOD( TwoDates[Column2], 1 ) – “08:00:00″*1)

  6. John Kelly says:

    Help please! Before I start I have your book, it’s been really helpful, but I have a challenge that I need to resolve quickly. It’s a variation on this theme… but I need to add x working days to a date. How can I modify this to add a number of working days to a date?

    • powerpivotpro says:

      In a calc column or a measure?

      • John Kelly says:

        Your very quick! It’s a column. So I have a date, and I want to add say, 15 working days to that date. I have been trying use filter and filter the dates table to only show working days then playing with dateadd within that filter but I think i am tackling it wrong.

        • powerpivotpro says:

          Wow that was a tough one. Not an elegant solution here, but I made two calc columns:

          [WorkdaysElapsed]=
          CALCULATE(SUM(Calendar[IsWorkday]), DATESBETWEEN(Calendar[Date], FIRSTDATE(ALL(Calendar[Date])), Calendar[Date]))

          And then [15WorkdaysLater]=
          =CALCULATE(LASTDATE(Calendar[Date]), FILTER(Calendar, Calendar[WorkdaysElapsed]=EARLIER(Calendar[WorkdaysElapsed])+15 && Calendar[IsWorkday]=1))

          • John Kelly says:

            Perfect! It took a while for me to get my head round what was happenning, but this works a treat, thank you sir! a bizarre aside, for some reason the FirstDate(all(Dates)) wasn’t working within the first calc. I’ve hardcoded the first date in my calendar to get it working, but it look syntactically correct, can’t figure that out.

            Hey ho, i’ll tackle that another time. It’s bedtime here in the UK. Thanks again mate, a wealth of knowledge, great support and your site is a great resource.

      • John Kelly says:

        I was thinking that something like this would work….

        =CALCULATE(Max(Dates[DateID]),
        DATESINPERIOD(filter(Dates[DateID], Dates[IsWorkDay] = 1),
        [OpenedDateTime], 15,day )
        )

        But I am not allowed to filter within the datesinperiod command, so I feel a little stuck.

  7. Drew says:

    What if your start and end dates are not the same for each “customer”. I have a list of customers that are in a certain program and I want to track how many days they’ve been in said program. I used a measure to find the unique start and end date for each customer and would like to use those in the measure you are writing about. When I show this in my pivot table it displays the program customers first with the correct number of days and then it shows all of the other customers that are not on the program with the total working days in the date table (2013 to 2014).

    Days on DLY Program:=CALCULATE(SUM(Calendar[WorkDaysWithHolidays]),DATESBETWEEN(Calendar[DateKey],[First Order Day on DLY Program],[Most Recent Order Day on DLY Program]))

    This is the measure I am using. Any help will be much appreciated.

Leave a Comment or Question