NETWORKDAYS Manipulation

[Mister Newbie]

Hi all.

Newbie user here.

=NETWORKDAYS(DATE($B$2,$B$3,E$8),DATE($B$2,$B$3,E$8),HolidayDates)

The above formula will list a 0 if it is SATURDAY, SUNDAY or a Holiday(based on a table), a 1 if it is MONDAY - FRIDAY.

How can I modify this to make SUNDAY, MONDAY, and the Holiday a 0?

I'm at my wits end on this one.

Thanks to all who is viewing this right now.

-M

 

[jorismoerings]

Hi,

The zero as a result come from the standard weekend parameter used in the NETWORKDAYS function.

Try changing you're NETWORKDAYS function towards NETWORKDAYS.INTL. It returns the number of whole workdays between two dates using parameters to indicate which and how many days are weekend days.
The function is available using a Excel 2010 or later.

Your formula would look like this:

=NETWORKDAYS.INTL(DATE($B$2,$B$3,E$8),DATE($B$2,$B$3,E$8),2,HolidayDates)

 

[Mister Newbie]

Hello Joris.

Thanks for the reply.

My apologies though. I am using Excel 2007. My heart broke when I saw your comment on "Excel 2010 or later".

I found an alternative for this in this same forum and an array formula is what was being recommended but all my attempts were futile.

Is there a way to still make this happen?

Thanks again everyone.

-M

Hi,

The zero as a result come from the standard weekend parameter used in the NETWORKDAYS function.

Try changing you're NETWORKDAYS function towards NETWORKDAYS.INTL. It returns the number of whole workdays between two dates using parameters to indicate which and how many days are weekend days.
The function is available using a Excel 2010 or later.

Your formula would look like this:

=NETWORKDAYS.INTL(DATE($B$2,$B$3,E$8),DATE($B$2,$B$3,E$8),2,HolidayDates)

 

[eggyjla]

This thread seems to be somewhat similar to http://www.mrexcel.com/forum/excel-questions/670652-challange-all-experts.html

 

[Mister Newbie]

Hello again.

Thanks for the replies.

It took awhile for me to figure assembling the tables, so my apologies.

Row 5 triggers the "1" and "0" based on this formula:

=NETWORKDAYS(DATE($B$2,$B$3,B$8),DATE($B$2,$B$3,B$8),HolidayDates)

My spreadsheet has conditional formatting(CF) and when it detects a zero, based on B5:L5 if it is a weekend it will be grayed out.
Here is the CF formula for the above:

=WEEKDAY(DATE($B$2,$B$3,B$8),2)>5

On top of this, I also have a "Holidays" table, if it finds that it is a zero BUT the date is a holiday, it turns red.
Here is the CF formula for the above:

=B$5=0

ROW 9 is where the problem lies...

ROW 9 is a Staggering schedule wherein Sundays and Mondays have a value of "0" and Saturday has a value of "1". Holidays are still 0.

Looking for an alternative for NETWORKDAYS.INTL

[table="width: 500, class: Grid, align: Left"]
[tr]
[td][/td]
[td]a[/td]
[td]b[/td]
[td]c[/td]
[td]d[/td]
[td]e[/td]
[td]f[/td]
[td]g[/td]
[td]h[/td]
[td]i[/td]
[td]j[/td]
[td]k[/td]
[td]l[/td]
[/color]
[/tr]
[tr]
[td]1[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]2[/td]
[td]Year[/td]
[td]2016[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]3[/td]
[td]Month[/td]
[td]1[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]4[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]5[/td]
[td][/td]
[td]0[/td]
[td]0[/td]
[td]0[/td]
[td]1[/td]
[td]1[/td]
[td]1[/td]
[td]1[/td]
[td]1[/td]
[td]0[/td]
[td]0[/td]
[td]1[/td]
[/tr]
[tr]
[td]6[/td]
[td][/td]
[td]Fri[/td]
[td]Sat[/td]
[td]Sun[/td]
[td]Mon[/td]
[td]Tue[/td]
[td]Wed[/td]
[td]Thu[/td]
[td]Fri[/td]
[td]Sat[/td]
[td]Sun[/td]
[td]Mon[/td]
[/tr]
[tr]
[td]7[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]8[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]9[/td]
[td][/td]
[td]0[/td]
[td]1[/td]
[td]1[/td]
[td]0[/td]
[td]1[/td]
[td]1[/td]
[td]1[/td]
[td]1[/td]
[td]1[/td]
[td]0[/td]
[td]0[/td]
[/tr]
[tr]
[td]10[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]11[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[/table]

 

[jorismoerings]

Hi,

When looking at your sample i'm curious to know why you need an alternative for the networkdays function. From my point of view you're only using it as some kind of validation of the criteria Workday or Not.
If it just validation; try this:

Book1
	B
14	1
Sheet1

Cell Formulas
Range	Formula
B14	=--NOT(OR(WEEKDAY(DATE($B$2,$B$3,B$8),1)=1,WEEKDAY(DATE($B$2,$B$3,B$8),1)=2,IFERROR(MATCH(DATE($B$2,$B$3,B$8),holidaydates,0)>0,0)))

Named Ranges
Name	Refers To	Cells
holidaydates	=Sheet1!$R$1:$R$10

else if you truly want an alternative for the networkdays function; try this:

Book1
	B
12	1
Sheet1

Cell Formulas
Range	Formula
B12	=IF(OR(DATE($B$2,$B$3,B$8)<=0,DATE($B$2,$B$3,B$8)<=0,DATE($B$2,$B$3,B$8)>DATE($B$2,$B$3,B$8),ISNUMBER(DATE($B$2,$B$3,B$8))=FALSE,ISNUMBER(DATE($B$2,$B$3,B$8))=FALSE),NA(),SUM(IF(ISERROR(MATCH(WEEKDAY(ROW(INDIRECT(DATE($B$2,$B$3,B$8)&":"&DATE($B$2,$B$3,B$8)))),{1;2},0)),IF(ISERROR(MATCH(ROW(INDIRECT(DATE($B$2,$B$3,B$8)&":"&DATE($B$2,$B$3,B$8))),holidaydates,0)),1,0)),0))

Named Ranges
Name	Refers To	Cells
holidaydates	=Sheet1!$R$1:$R$10

 

[Mister Newbie]

SON OF A BUILDING BLOCK! IT WORKS!

Yes. I was going for validation and both formulas work like a charm!

Thanks Joris! ray:

Hi,

When looking at your sample i'm curious to know why you need an alternative for the networkdays function. From my point of view you're only using it as some kind of validation of the criteria Workday or Not.
If it just validation; try this:

Book1
	B
14	1
Sheet1

Cell Formulas
Range	Formula
B14	=--NOT(OR(WEEKDAY(DATE($B$2,$B$3,B$8),1)=1,WEEKDAY(DATE($B$2,$B$3,B$8),1)=2,IFERROR(MATCH(DATE($B$2,$B$3,B$8),holidaydates,0)>0,0)))

Named Ranges
Name	Refers To	Cells
holidaydates	=Sheet1!$R$1:$R$10

else if you truly want an alternative for the networkdays function; try this:

Book1
	B
12	1
Sheet1

Cell Formulas
Range	Formula
B12	=IF(OR(DATE($B$2,$B$3,B$8)<=0,DATE($B$2,$B$3,B$8)<=0,DATE($B$2,$B$3,B$8)>DATE($B$2,$B$3,B$8),ISNUMBER(DATE($B$2,$B$3,B$8))=FALSE,ISNUMBER(DATE($B$2,$B$3,B$8))=FALSE),NA(),SUM(IF(ISERROR(MATCH(WEEKDAY(ROW(INDIRECT(DATE($B$2,$B$3,B$8)&":"&DATE($B$2,$B$3,B$8)))),{1;2},0)),IF(ISERROR(MATCH(ROW(INDIRECT(DATE($B$2,$B$3,B$8)&":"&DATE($B$2,$B$3,B$8))),holidaydates,0)),1,0)),0))

Named Ranges
Name	Refers To	Cells
holidaydates	=Sheet1!$R$1:$R$10

 

[jorismoerings]

We got there in the end. Glad I could help.