Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 554
  • Last Modified:

NETWORKDAYS substitute

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
bromy2004
Asked:
bromy2004
  • 12
  • 10
  • 3
1 Solution
 
Chris BottomleyCommented:
Hello bromy2004,

A standard formula would be:

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

Regards,

chris_bottomley
0
 
bromy2004Author Commented:
But that doesn't handle Holidays.
i have Holidays as a Named range with the Next Years Public Holidays.
0
 
bromy2004Author Commented:
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

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
zorvek (Kevin Jones)ConsultantCommented:
I realize you have found a VBA solution, but would you like a formula that works?

Kevin
0
 
bromy2004Author Commented:
Yes please Kevin.
0
 
zorvek (Kevin Jones)ConsultantCommented:
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
 
zorvek (Kevin Jones)ConsultantCommented:
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
 
Chris BottomleyCommented:
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
 
bromy2004Author Commented:
@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
 
zorvek (Kevin Jones)ConsultantCommented:
Yanks beat Brits again! Woohoo!
0
 
zorvek (Kevin Jones)ConsultantCommented:
>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
 
zorvek (Kevin Jones)ConsultantCommented:
>Yours is exactly the same as Kevin's

You're my buddy now ;-)
0
 
zorvek (Kevin Jones)ConsultantCommented:
>The array didn't. 21 Days

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

Kevin
0
 
bromy2004Author Commented:
Kevin, please reset the Question.
and I missed a cell Reference.
SUM((ROW(INDIRECT(A1&":"&B1))=TRANSPOSE(H1:H20))*1)
B1 had changed to A1

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
 
bromy2004Author Commented:
Aussie Format
d/mm/yyyy
0
 
zorvek (Kevin Jones)ConsultantCommented:
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
 
bromy2004Author Commented:
Perfect Thanks.

Ill award Points
0
 
bromy2004Author Commented:
Problem was Self-Solved.
Kevin provided alternatives
0
 
Chris BottomleyCommented:
>>> 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
 
zorvek (Kevin Jones)ConsultantCommented:
OK, that, by itself and without context, is just too dang funny!

Kevin's Dog
0
 
bromy2004Author Commented:
And Aussie beats all
0
 
zorvek (Kevin Jones)ConsultantCommented:
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
 
zorvek (Kevin Jones)ConsultantCommented:
Is this question closed?
0
 
bromy2004Author Commented:
Yes Question Closed, Points awarded to you.
The Water thing is a Myth.
Everything else on the other hand.....
0
 
zorvek (Kevin Jones)ConsultantCommented:
:-)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 12
  • 10
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now