Calculating NETWORKDAYS when people work just 1 to 4 days/wk or have non-consecutive days off

This video starts with a correction from my previous video. I was wrong about how NETWORKDAYS works and I should have used NETWORKDAYS.INTL because it allows for "weekends" that are other than Saturday-Sunday. Thus, if you work at a restaurant that's only open Tuesday thru Sunday, NETWORKDAYS.INTL will let you choose Monday as your weekend.

Now, here's a challenge. What about part-time people who work Mondays, Wednesdays and Fridays? Or! When I was living in California, I worked 4 days, 10 hours each, for a full 40 hours. I loved working Monday, Tuesday, Thursday Friday. I was off on Wednesdays, Saturdays and Sundays.

To calculate the net work days in such situations NETWORKDAYS.INTL can't help us.

In this video I show how to achieve this by using Power Query (unpivot), FILTER and COUNTIF. I also make clever use of the MATCH function to identify holidays.

#NETWORKDAYS
#3DayWeekends
#FILTERfunction

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

[wayneedmondson]

Hi Oz. Cool example. I came up with the following. On your Main worksheet, in cell O12, enter the following formula and copy down to O24: =NETWORKDAYS.INTL($C$6, $C$248, RIGHT(TEXTJOIN("", FALSE, IF(Table4[@[1]:[7]]="", 1, 0)), 6)&LEFT(TEXTJOIN("", FALSE, IF(Table4[@[1]:[7]]="", 1, 0)), 1), Table3[Date1]).
The NEWWORKDAYS.INTL function has optional weekend string values of 1 and 0. They always start with Monday. Example: the string is workdays M-F and weekend S-S. You can use any combination of 1 and 0 to indicate which day is a workday (a 0) and which day is a weekend (a 1). My formula above, looks at your table of workdays for each employee. Using IF and TEXTJOIN, I convert blanks to 1s and x's to 0s which gives me the 7 digit string value. Since you started the table from SUN and I need the string values to start from MON, I used RIGHT and LEFT to reposition the first string value to the end. This then becomes the function argument for [weekend]. For the balance, I just point to your starting and ending dates and range of holidays. Voila! The net work days for each employee is shown in O12 to O24.. no Power Query needed. The possibilities of combinations of EXCEL functions is endless. Always fun to figure out a new way to make them work. Hope someone finds it useful. Thanks always for your entertaining and interesting videos and topics :)) Thumbs up!!

[patrickschardt]

You found a mistake or a pissing piece of information. So then you came back to fix the mistake and add the missing piece. Not many people do that so thumbs up for that

Thank for the tutorial Uncle Oz

[spilledgraphics]

Oz.... loving the disco ball on minute 3:13 . 🔥🔥🔥 perfect for spreadsheet dancing 😎👌

[BillSzysz]

This is an interesting case, Oz. Thanks. :-))
From me, a single-cell solution (without calendar table) "Start date" in B2 and "End date" in C2. We can change the sort order by changing constant array (second argument of SORTBY). If the range G11:M11 could be ordered (from Monday to Sunday) then SORTBY function woul not be needed.
=NETWORKDAYS.INTL(B2, C2, TEXTJOIN("", , SORTBY(--ISNONTEXT(FILTER(G12:M24, F12:F24=F2)), {7, 1, 2, 3, 4, 5, 6})), Table3[Date1])