Solved

# NETWORKDAYS substitute

Posted on 2010-01-06
534 Views
What can i use for a NETWORKDAYS substitute.

My spreadsheet contains extensive Macros and Sheet formulae and i can't always assume the User has (or knows how to) installed the Analysis toolpak.

i would prefer VBA, but a traditional formula would be ok.

it must be able to handle Holidays.
0
Question by:bromy2004

LVL 59

Expert Comment

Hello bromy2004,

A standard formula would be:

=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(STartDate & ":" & endDate)))={2,3,4,5,6})*1)

Regards,

chris_bottomley
0

LVL 10

Author Comment

But that doesn't handle Holidays.
i have Holidays as a Named range with the Next Years Public Holidays.
0

LVL 10

Author Comment

Have figured it out.

``````Public Function DaysInMonth(dt As Date, Holidays) As Integer
Dim StartDate   As Date
Dim EndDate     As Date
Dim i           As Long
Dim j           As Long
Dim WkEnd       As Boolean
Dim Holiday     As Boolean
Dim MyHolidays() As Date
For i = 1 To Holidays.Cells.Count
If Holidays(i) <> 0 Then
j = j + 1
ReDim Preserve MyHolidays(1 To j)
MyHolidays(j) = Holidays(i)
End If
Next

StartDate = DateSerial(Year(dt), Month(dt), 1)
EndDate = DateSerial(Year(dt), Month(dt) + 1, 0)

'Weekdays
For i = StartDate To EndDate
WkEnd = False
If Weekday(i, 2) > 5 Then WkEnd = True

For j = LBound(MyHolidays) To UBound(MyHolidays)
If Holidays(j) = i Then Holiday = True
Next j
If Not WkEnd And Not Holiday Then DaysInMonth = DaysInMonth + 1
Next
End Function
``````
0

LVL 81

Expert Comment

I realize you have found a VBA solution, but would you like a formula that works?

Kevin
0

LVL 10

Author Comment

0

LVL 81

Expert Comment

Put your holidays in cells H1:H20. Assuming your base date that determines the month is in A1, use this array formula to determine the number of work days in that month, excluding holidays.

=SUM((WEEKDAY(ROW(INDIRECT(DATE(YEAR(A1),MONTH(A1),1)&":"&DATE(YEAR(A1),MONTH(A1)+1,0))))={2,3,4,5,6})*1)-SUM((ROW(INDIRECT(A1&":"&B1))=TRANSPOSE(H1:H20))*1)

To enter an array formula, press CTRL+SHIFT+ENTER.

Kevin
0

LVL 81

Accepted Solution

A non-array version of same:

=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(DATE(YEAR(A1),MONTH(A1),1)&":"&DATE(YEAR(A1),MONTH(A1)+1,0))))={2,3,4,5,6})*(COUNTIF(H1:H20,ROW(INDIRECT(DATE(YEAR(A1),MONTH(A1),1)&":"&DATE(YEAR(A1),MONTH(A1)+1,0))))=0))

Kevin
0

LVL 59

Expert Comment

I was about to post and since it looks different I will:

=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(Start & ":" & End)))={2,3,4,5,6})*(COUNTIF(Holidays_First:Holidays_Last,ROW(INDIRECT(Start & ":" & End)))=0))

STart/end are the discrete cells, (either name them or replace with addresses) with the first and last date of the period and holidays first/last is a row/column range with the holidays one per cell.

Chris
0

LVL 10

Author Comment

@Chris
Yours is exactly the same as Kevin's

@Kevin
The Non-Array works Fine. 19 Days
The array didn't. 21 Days
NetworkDays.    19 Days
0

LVL 81

Expert Comment

Yanks beat Brits again! Woohoo!
0

LVL 81

Expert Comment

>The array didn't. 21 Days

It worked at my house! What the heck did you do to it?

Want me to reset the question?

Kevin
0

LVL 81

Expert Comment

>Yours is exactly the same as Kevin's

You're my buddy now ;-)
0

LVL 81

Expert Comment

>The array didn't. 21 Days

Never mind. It's messed up like my life.

Kevin
0

LVL 10

Author Comment

and I missed a cell Reference.
SUM((ROW(INDIRECT(A1&":"&B1))=TRANSPOSE(H1:H20))*1)

But still not correct.
it only returned 20 Days this time.

These are the values in H
24/04/2009
1/06/2009
28/09/2009
25/12/2009
26/12/2009
28/12/2009
1/01/2010
26/01/2010
1/03/2010
2/04/2010
5/04/2010
25/04/2010
26/04/2010
7/06/2010
27/09/2010
25/12/2010
26/12/2010
27/12/2010
28/12/2010
1/01/2011
3/01/2011
0

LVL 10

Author Comment

Aussie Format
d/mm/yyyy
0

LVL 81

Expert Comment

OK, I fixed the array version but now it's as long as the non-array version so what's the point. Here it is for the hell of it:

=SUM((WEEKDAY(ROW(INDIRECT(DATE(YEAR(A1),MONTH(A1),1)&":"&DATE(YEAR(A1),MONTH(A1)+1,0))))={2,3,4,5,6})*1)-SUM((ROW(INDIRECT(DATE(YEAR(A1),MONTH(A1),1)&":"&DATE(YEAR(A1),MONTH(A1)+1,0)))=TRANSPOSE(H1:H20))*1)

Kevin
0

LVL 10

Author Comment

Perfect Thanks.

Ill award Points
0

LVL 10

Author Closing Comment

Problem was Self-Solved.
Kevin provided alternatives
0

LVL 59

Expert Comment

>>> Yanks beat Brits again! Woohoo!

I guess from that statement that Kevins Kitty Cat is one of the natives in new England.  And if so he/she merely posted a response, (alright then dozens of responses - perhaps it is Kitty Cat's) earlier than I.  Better no way! ... more determined yes ;)

Chris
0

LVL 81

Expert Comment

OK, that, by itself and without context, is just too dang funny!

Kevin's Dog
0

LVL 10

Author Comment

And Aussie beats all
0

LVL 81

Expert Comment

Bunch of convicts hanging upside down. And driving on the wrong side of the road. Water swirls the wrong way too. And what's with the monopoly on venomous snakes?
0

LVL 81

Expert Comment

Is this question closed?
0

LVL 10

Author Comment

Yes Question Closed, Points awarded to you.
The Water thing is a Myth.
Everything else on the other hand.....
0

LVL 81

Expert Comment

:-)
0

## Featured Post

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,â€¦
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most pâ€¦
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo â€¦
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This lâ€¦