Networkdays( ) function to include Saturdays

[rahul_chatterjee]

Dear All,

We have a six day work week and I would like networkdays()function
count Saturday as a work day while calculating days in between two
dates. Any idea how I may do this.

Thank you in advance.

Regards,
Rahul

 

[Ron Rosenfeld]

Dear All,

We have a six day work week and I would like networkdays()function
count Saturday as a work day while calculating days in between two
dates. Any idea how I may do this.

Thank you in advance.

Regards,
Rahul

Assuming that you may also have Holidays that you wish to exclude, the
following *array* formula may do what you wish:

=SUM(IF((WEEKDAY(ROW(INDIRECT(StartDate&":"&EndDate)))<>1)
*ISNA(MATCH(ROW(INDIRECT(StartDate&":"&EndDate)),Holidays,0)),1,0))

Holidays is a named range containing any Holiday dates. The formula will
ignore a Holiday if it happens to fall on a Sunday.

To enter an *array* formula, after typing or pasting the formula into the
formula bar, hold down <ctrl><shift> while hitting <enter>. XL will place
braces {...} around the formula.


--ron

 

[Myrna Larson]

Here's a message that I posted way back in June, 1999. I found it with a
Google newsgroup search.

I modified the code to handle a 6-day work-week. The only changes needed were
to change 5 to 6 in two places.

I haven't tried the code since I modified it. Please let me know if it works
correctly.

Old message and coded follows:

"Some months ago there was a discussion in one of the VB ng's re coding an
equivalent to the NETWORKDAYS function in the ATP. I finally finished up the
following code, for XL97. It might be useful in XL if you don't need the ATP
for other functions.

The function to be used from the worksheet is xNetWorkdays. The syntax is the
same as the NETWORKDAYS function in the ATP. Actually it's a tad faster than
the ATP function -- runs in about 3/4 of the time.

Function xNetWorkDays(ByVal sDate As Date, ByVal eDate As Date, _
Optional Holidays As Range = Nothing) As Long

'XL function to replace NETWORKDAYS routine in ATP
'NOTE: list of holidays MUST be sorted in ascending order
'and must *NOT* include any holidays that fall on Sunday
'since Sundays are already excluded

Dim Swapped As Boolean
Dim dTemp As Date
Dim FullWeeks As Long
Dim D As Long
Dim W As Long
Dim Holiday1 As Long
Dim Holiday2 As Long

'check arguments
'swap the dates if necessary
Swapped = False
If sDate > eDate Then
dTemp = sDate
sDate = eDate
eDate = dTemp
Swapped = True
End If

FullWeeks = (eDate - sDate) \ 7
'for now, assume there weren't any holidays included
D = FullWeeks * 6 'CHANGED 5 to 6 HERE!!!

'count weekdays in the final partial week, if any
dTemp = sDate + FullWeeks * 7

'assign number to day of week, using non-standard
'scheme: Mon = 0, Sun = 6
W = WeekDay(sDate, vbMonday) - 1
Do While dTemp <= eDate
If W < 6 Then D = D + 1 'CHANGED 5 to 6 HERE!!!
W = (W + 1) Mod 7
dTemp = dTemp + 1
Loop

If Not Holidays Is Nothing Then
'get pointer to 1st holiday that's on or *later* than sDate
Holiday1 = FindHoliday(sDate, Holidays, True)
'get pointer to 1st holiday that's on or *earlier* than eDate
Holiday2 = FindHoliday(eDate, Holidays, False)
'subtract number of holidays between sDate and eDate, inclusive
D = D - (Holiday2 - Holiday1 + 1)
End If

If Swapped Then D = -D
xNetWorkDays = D

End Function 'xNetWorkDays

Private Function FindHoliday(ByVal TheDate As Date, HolidayList As Range, _
Optional GoHigher As Boolean = False) As Long
'binary search used by xNetWorkDays
Dim L As Long, U As Long, M As Long

L = 1
U = HolidayList.Rows.Count

Do While U >= L
M = (U + L) \ 2
Select Case HolidayList.Cells(M, 1).Value
Case Is < TheDate
L = M + 1
Case Is > TheDate
U = M - 1
Case Else
FindHoliday = M
Exit Function
End Select
Loop

'pointers have crossed: L > U
If GoHigher Then
FindHoliday = L
Else
FindHoliday = U
End If

End Function

 

[hgrove]

Ron Rosenfeld wrote...

(e-mail address removed)[/email] (rahul_chatterjee) wrote: ...

We have a six day work week and I would like networkdays()
function count Saturday as a work day while calculating days
in between two dates. Any idea how I may do this.

...
Assuming that you may also have Holidays that you wish to
exclude, the following *array* formula may do what you wish:

=SUM(IF((WEEKDAY(ROW(INDIRECT(StartDate&":"&EndDate)))
<>1)*ISNA(MATCH(ROW(INDIRECT(StartDate&":"&EndDate)),
Holidays,0)),1,0))

...

Why an array formula?

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(StartDate&":"&EndDate)))<>1))
+(NETWORKDAYS(StartDate,EndDate,Holidays)-NETWORKDAYS(StartDate,EndDate))

For that matter, this could be calculated directly.

=INT((EndDate-StartDate)*6/7)+(WEEKDAY(StartDate)>1)+
(WEEKDAY(EndDate)>WEEKDAY(StartDate))
+(NETWORKDAYS(StartDate,EndDate,Holidays)-NETWORKDAYS(StartDate,EndDate)

 

[hrlngrv - ExcelForums.com]

Ron Rosenfeld wrote..

(e-mail address removed) (rahul_chatterjee) wrote ..

Assuming that you may also have Holidays that you wish to exclude th
following *array* formula may do what you wish

=SUM(IF((WEEKDAY(ROW(INDIRECT(StartDate&":"&EndDate)))<>1
*ISNA(MATCH(ROW(INDIRECT(StartDate&":"&EndDate)),Holidays,0))
1,0)

..

Don't know whether my other response will come through or no
(ExcelForum in the singular is down again), but I screwed up th
holiday count

Array formulas aren't needed. Eithe

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(StartDate&":
&EndDate)))<>1))-SUMPRODUCT((Holidays>=StartDate
*(Holidays<=EndDate)*(WEEKDAY(Holidays)<>1)

or direct cal

=INT((EndDate-StartDate)*6/7)+(WEEKDAY(StartDate)>1
+(WEEKDAY(EndDate)>WEEKDAY(StartDate)
-SUMPRODUCT((Holidays>=StartDate)*(Holidays<=EndDate
*(WEEKDAY(Holidays)<>1)

 

[hrlngrv - ExcelForums.com]

Myrna Larson wrote..
..

'NOTE: list of holidays MUST be sorted in ascending orde
'and must *NOT* include any holidays that fall on Sunda
'since Sundays are already exclude

..

Isn't this unreasonably restrictive

It's unlikely the holiday range would be more than a dozen dates pe
year, so it wouldn't take much time to iterate through it in it
entirity. As for needing to be sorted, all that's needed is checkin
whether a holiday falls between the starting and ending dates an
whether it falls on a workday

 

[hrlngrv - ExcelForums.com]

I couldn't resist writing my own

Function nwds(
sd As Date,
ed As Date,
Optional hd As Variant,
Optional wd As Variant
) As Lon
'---------------------------------------------------------------
'works like NETWORKDAYS when passed the first 2 or 3 argument
'accepts an optional fourth argument which should be an arra
'of weekday numbers (US convention: Sun = 1,..., Sat = 7
o
'weekday names or abbreviations (2 chars needed for Sat and Th
'since InStr will return Sun for 'S' and Tue for 'T'; weekda
'names must also be Proper Case
'---------------------------------------------------------------
Dim i As Long, n As Long, x As Variant, ud(1 To 7) A
Boolea

If IsEmpty(wd) Then wd = Array(2, 3, 4, 5, 6)
'defaults to M-
If TypeOf wd Is Range Then wd = wd.Valu
If Not IsArray(wd) Then wd = Array(wd

'process the wd array - die on invalid entrie
For Each x In w
If IsNumeric(x) The
i = CLng(x
Els
i = Int((InStr(1,
"Sunday Monday Tuesday WednesdayThursday Friday Saturda
",
x) + 8) / 9
End I

If i < 1 Or i > 7 Then GoTo Invali

If Not ud(i) The
n = n +
ud(i) = Tru
End I

Next

'initial estimate - whole week
nwds = Int((ed - sd) / 7#) *

'refinements: add partial weeks at beginning and/or en
If WeekDay(sd, vbSunday) <= WeekDay(ed, vbSunday
The
For i = WeekDay(sd, vbSunday) To WeekDay(ed
vbSunday
If ud(i) Then nwds = nwds +
Next

Els
For i = WeekDay(sd, vbSunday) To vbSaturda
If ud(i) Then nwds = nwds +
Next

For i = vbSunday To WeekDay(ed, vbSunday
If ud(i) Then nwds = nwds +
Next

End I

'remove holidays falling on workday
If Not IsMissing(hd) The
For Each x In h
If VarType(x) <> vbDate Then GoTo Invali
If x >= sd And x <= ed And ud(WeekDay(x
vbSunday)) Then
nwds = nwds -
Next
End I

Exit Functio

Invalid
nwds = -1 'error return valu
Exit Functio
End Functio

 

[Myrna Larson]

Yeah, I can write the code to check the holiday list one entry at a time, and
for a short list, it may OK.

But when I wrote this code back in 1999, I was more interested in speed than
anything else, so I used a binary search to identify the first holiday >= the
start date, and the last holiday <= the end date. The number of holidays is
then obtained by simple subtraction.

What I ended up with was, at that time, actually faster than MATCH or VLOOKUP
on a sorted list with a final argument of TRUE. AIR, that's no lnger true. MS
seems to have done some reworking of the code for those functions.

Personally, I don't see any huge problem with modifying the list, then
clicking the sort button on the toolbar. If you set up your list just once a
year, it's a once-a-year job <g>.

Of course I generate my own list, for a 10 year period (~110 cells), with
macro code. I've posted that code in the past but didn't include here, since I
think the OP is not from the US -- we don't have 6-day workweeks.

 

[Harlan Grove]

Yeah, I can write the code to check the holiday list one entry at a time,
and for a short list, it may OK.

....

I actually considered the restriction that holidays couldn't fall on Sundays
to be the unreasonable one. Sorting the holidays, as you point out, is a
once-off chore.

 

[Myrna Larson]

Definitely, without that restriction you've had to examine every cell in the
holiday list, checking whether it's in the target range and that it's not a
Sunday. With 10-11 cells, that should be OK. With a longer list, IMO, that's
too slow, but it's certainly possible.

BTW, the code I've posted in the past to generate the holiday list given a
range of years, automatically moves a holiday that falls on Sat or Sun to Mon,
thus eliminating this problem before it starts <g>.

Another way to remove sunday holidays would be a helper column:

=IF(WEEKDAY(A2)=7,"",A2)

and specify the column with that formula as the holiday list.

Caveat: I haven't tried that to see if the builtin WEEKDAY and NETWEEKDAY
functions that use a holiday list will ignore "blanks" in the list.

A totally different approach to the original question is to use the built-in
NETWORKDAYS function and *add back* the number of Saturdays.

There have been formulas and VBA posted to count the number of a particular
weekday that occur in a timespan. In fact I just went to Google to look for
them, and I came up with a message from 2001, with code by me, and a formula
by Tom to calculate the number of Mondays. I haven't studied his formula hard
enough to know how to modify it to count Saturdays.

But if your holiday list includes Saturday holidays, you have the same problem
again.

Tom's formula to calculate number of Mondays, from 2001:

Here is a worksheet function I was playing around with - not polished, but
seems to work:
Start date in B3, End Date in B4
=IF(WEEKDAY(B3)=1,1,0)+INT((B4-B3)/7)+((MOD(B4-B3,7)+WEEKDAY(B3))>7)

 

[Vasant Nanavati]

I had posted the following a few months ago:

=NETWORKDAYS(A1,B1,HOLIDAYS)+INT((B1-A1)/7)+IF(WEEKDAY(A1)>6-MOD(B1-A1,7),1,
0)-SUM((WEEKDAY(HOLIDAYS)=6)*1)

but never got any feedback on whether it worked or not.

If it does work, it looks to be a bit shorter than Harlan's <g>.

--

Vasant

Definitely, without that restriction you've had to examine every cell in the
holiday list, checking whether it's in the target range and that it's not a
Sunday. With 10-11 cells, that should be OK. With a longer list, IMO, that's
too slow, but it's certainly possible.

BTW, the code I've posted in the past to generate the holiday list given a
range of years, automatically moves a holiday that falls on Sat or Sun to Mon,
thus eliminating this problem before it starts <g>.

Another way to remove sunday holidays would be a helper column:

=IF(WEEKDAY(A2)=7,"",A2)

and specify the column with that formula as the holiday list.

Caveat: I haven't tried that to see if the builtin WEEKDAY and NETWEEKDAY
functions that use a holiday list will ignore "blanks" in the list.

A totally different approach to the original question is to use the built-in
NETWORKDAYS function and *add back* the number of Saturdays.

There have been formulas and VBA posted to count the number of a particular
weekday that occur in a timespan. In fact I just went to Google to look for
them, and I came up with a message from 2001, with code by me, and a formula
by Tom to calculate the number of Mondays. I haven't studied his formula hard
enough to know how to modify it to count Saturdays.

But if your holiday list includes Saturday holidays, you have the same problem
again.

Tom's formula to calculate number of Mondays, from 2001:

Here is a worksheet function I was playing around with - not polished, but
seems to work:
Start date in B3, End Date in B4
=IF(WEEKDAY(B3)=1,1,0)+INT((B4-B3)/7)+((MOD(B4-B3,7)+WEEKDAY(B3))>7)

 

[fernando cinquegrani]

Dear All,

We have a six day work week and I would like networkdays()function
count Saturday as a work day while calculating days in between two
dates. Any idea how I may do this.

=INT((6*enddate)/7)-INT((6*startdate+1)/7)+1

see:
http://groups.google.com/groups?selm=#[email protected]
http://www.prodomosua.it/zips/delledate.xls (inizio :: startdate, fine :: enddate, festività :: holidays)
.f
http://www.prodomosua.it