**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:

**Desired Result**

So how do we get to this?

### Date Table!

As 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

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:

=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

Just for completeness:

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

Please this formula is suppose to be used for calculating the weekend & Holiday, It is giving me error after relating the Date table with the Holiday table.

I would appreciate your kind assistance on how to resolve this error

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

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.

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).

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!

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.

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?

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.

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)

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)

I can’t seem to get this to work? I get all sorts of errors. Has anyone successfully used this?

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?

In a calc column or a measure?

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.

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))

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.

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.

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.

Working with PowerBI, I’m trying to work out a way to show the last 4 business days as well as the weekend if it falls within the last 4 business days. So say it is Monday today and I want to display the figures from last Tuesday plus the weekend but this coming Friday I only want the figures since today (Monday). Any ideas? It’s for the credit card payments that take 4 days to clear into our bank account so we can easily see what balance is outstanding from our cash flow. Also the workbook in the above example does not like Excel 2016… It refuses to open.

Great post Rob, thanks for sharing!

I am receiving an error that states that one of my date columns is “Calculation error in column ‘TwoDates'[]: An invalid numeric representation of a date value was encountered. This begs to question how this approach can be used if one of the two date columns has a blank or missing value in it. Is there an elegant way to skip rows that do not have a date, without creating a new/cleaned date column?

I am getting the following error: “A single value for column ‘StartDate’ in table ‘OrderDuration’ cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.”

My startDate is not unique as this is a list of orders and it is possible the same start date is on more than one order how can I go about getting the number of work days between dates.?

I figured out what I was doing wrong I was trying to use this as a measure but should have been using it to add a new column . All is good now that it is used as a column

Excellent post -Thank you!

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

Please I used this formula for isHoliday Column in Powerpivot Calendar Table, I got an Error message.

I would appreciate your understanding, assistance and support in resolving this error.

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

I got my data model corrected, The formula is absolutely correct. I really appreciate this free and kind support in improving my DAX knowledge.

I have been using a variation to calculate Work Hours from Start to finish on Working Days, but I have noticed that the formula takes significant processing power…

I have noticed using DAX Studio the section that takes a major toll on my report is the DatesBetween (Var DaysWork)…

=

// FindStartHour starting at 8am

VAR HourStart =

IF (

HOUR ( FactTable[Start_Date] ) 18,

18,

HOUR ( FactTable[Start_Date] )

)

)

// Find End Hour if Hour is after 6pm or before 8am

VAR HourEnd =

IF (

HOUR ( FactTable[End_Date] ) 18,

18,

HOUR ( FactTable[End_Date] )

)

)

// Get WorkingDays

VAR DaysWork =

CALCULATE (

SUM ( DateCalendar[IsWorkDayNum] ) – 1,

DATESBETWEEN (

DateCalendar[FullDate],

FactTable[Start_Date],

FactTable[End_Date]

)

)

RETURN

// Get Working Days with complete working hours and then add days starting and ending hours

( DaysWork * 10 ) + ( ( 18 – HourStart ) – ( 18 – HourEnd ) )

I am reviewing two schedules where sometimes the date goes back into time. To be more specific, I noticed that column1 must always take place before the column2 date. What happens if column2 takes place before column1? The function returns a 0 when I am hoping for a negative number. Does anyone have any suggestions for this? Thanks!

Did you get an answer for the negative number?

Does this work with the two dates being in different years?

_dc_Vol_TTR_BDays =

// BusinessDays

// You can change the IF statement to cater to any logic for [BDay].

SUMX(

SELECTCOLUMNS(

CALENDAR([_scCreatedDateYMD], [_scLastActiveDateYMD]),

“Date”, [_scCreatedDateYMD],

“BDay”, IF(WEEKDAY([Date],3) < 5, 1, 0)

),

[BDay]

)

I created a two column table (Calendar) in Excel with all the dates (“Date”) from 2018 to 2020 and added a column (“IsweekDay” where I put 1 if it is a work day and 0 if it is a day off (weekend/holiday) and uploaded to power BI.

Now I am trying to calculate the number of business days between 2 date fields that I already have: (“Last sales day” and “last business day”)

Number of Days = CALCULATE(SUM(‘Calendar'[IsWeekDay]),

DATESBETWEEN(‘Calendar'[Date],

‘Commissions Master'[last sales day],

‘Commissions Master'[LastBusinessDay]))

The result is wrong. instead of 1 days I am receiving 17 days and money other odd results.

Could you please help me, what is wrong in my code?