Networkdays formula - need help

[Lukael]

Hi,

I have dates in column A, from 1st January to 31th December (A1:A365).

I want to get Networkdays for each month, but not just with simple determining ranges for each month. What I want is that formula
would check start day and end day of month, and then calculate Networkdays. This is because I have sheets with different years, and
changing formula in each sheets would take me ages.

This is what I got so far :

Please Login or Register  to view this content.

Formula is set for January, and result is 22 which is wrong, 21 is correct result because 1 day of holiday is between.

Anybody have an idea how to fix this ??

[AlKey]

Formula will return 21 days when you add your holiday list of dates.

=NETWORKDAYS(A1,B1,"1/1/2015")

Data Range

	A	B	C
1	1/1/2015	1/31/2015	21

[Lukael]

looks like you didn't read my post#1....

I know that, but I want formula to find start/end of month on its own. I have all dates in column A, not separated. List of holidays with dates was allready created before...

[Jonmo1]

If A1 is the Year and A2 is the Month Number, try

=NETWORKDAYS(DATE(A1,A2,1),DATE(A1,A2+1,0),Holidays)

[AlKey]

Ok, I see what you meen (I think)

the start date

=MIN(IF(MONTH(A2:A24)=1,A2:A24))

end date

=Max(IF(MONTH(A2:A24)=1,A2:A24))

both formulas array entered.

[Lukael]

Hi Jonmo1,

As I told I have dates in column A - like this:

01.01.2015
02.01.2015
03.01.2015 etc.

I formatted A1 as "yyyy" to show year, and A2 as "mm" to show month number....And tried your formula, but not working, I get #Num error

[Jonmo1]

Can you post a sample book

[AlKey]

This array formula will return the number of days (21 for january)

where "1/1/2015" is a holiday

=NETWORKDAYS(MIN(IF(MONTH(A2:A24)=1,A2:A24)),MAX(IF(MONTH(A2:A24)=1,A2:A24)),"1/1/2015")

***Array formula must be entered by using key combination of CTRL+SHIFT+ENTER and not just ENTER

[Lukael]

ok, but only dates, nothing else...

[Lukael]

Hi Alkey,

Don't quite follow your fomula but on first sight I would say that you determine a range for month to check. It must check all dates in range A1:A365, including Feb, March etc. Your formula produces me a result of 16.

[Jonmo1]

Can you include what your expected result is?

It's unclear exactly what you want to happen.

[Lukael]

1st January is holiday, ofcourse. January has 31 days, and 21 working days. It's just that what I want - and for all other months ofcourse (formula will determine what is holiday).

Problem is that dates all together in one column, you see that in sample. I can't change that. And sheets are made for years 2000-2020, so I would need to create many different formulas for each sheet.

[Lukael]

1st January is holiday, ofcourse. January has 31 days, and 21 working days. It's just that what I want - and for all other months ofcourse (formula will determine what is holiday).

Problem is that dates all together in one column, you see that in sample. I can't change that. And sheets are made for years 2000-2020, so I would need to create many different formulas for each sheet.

[AlKey]

Hi Alkey,

Don't quite follow your fomula but on first sight I would say that you determine a range for month to check. It must check all dates in range A1:A365, including Feb, March etc. Your formula produces me a result of 16.

Did you change ranges? You need to change A2:A24 to A1:A365

[Jonmo1]

Unfortunately, You have to specify the Holidays.
There's no built in function to tell Excel which days are holidays.
Simply because the definition of Holiday varies so much from city to state to country, and not even all businesses recongnize the same holidays.
So you have to create your own range of cells that contain designated holiday dates to exclude.

So this would do January, based on the year given in A1
=NETWORKDAYS(DATE(YEAR($A$1),1,1),EOMONTH(DATE(YEAR($A$1),1,1),0),$M$1:$M$20)

The Red 1's are what you have to change for each month
M1:M20 is the range containing your list of holiday dates (you can put it anywhere you want).

[Lukael]

Did you change ranges? You need to change A2:A24 to A1:A365

yes i did that too

[Lukael]

Unfortunately, You have to specify the Holidays.
There's no built in function to tell Excel which days are holidays.
Simply because the definition of Holiday varies so much from city to state to country, and not even all businesses recongnize the same holidays.
So you have to create your own range of cells that contain designated holiday dates to exclude.

So this would do January, based on the year given in A1
=NETWORKDAYS(DATE(YEAR($A$1),1,1),EOMONTH(DATE(YEAR($A$1),1,1),0),$M$1:$M$20)

The Red 1's are what you have to change for each month
M1:M20 is the range (you can put it anywhere you want) containing your list of holiday dates.

I'm sorry, but I still get result 22. See attached sample !

[Jonmo1]

You didn't change the range for the holidays.
The formula still references M1:M20, but you put the holiday dates in M316:M328

[Lukael]

oh my god, sorryyyyy... I really didn't notice that. It works, finally !! thanks a lot !!!

[Jonmo1]

You're welcome.