WORKDAY() Function Equivalent with SUMPRODUCT()
 

I am looking for an equivalent to the WORKDAY() function that does not need
the analysis toolpak. I send a spreadsheet that uses it extensively, to many
people, many of whom do not have the toolpak set up. I think there is an
equivalent that uses SUMPRODUCT(), but I cannot find it. I have a table of
holidays I can use, and I can set up a table of weekends if I need to.

WORKDAY() Function Equivalent with SUMPRODUCT()
 

This is from Ron Rosenfeld

--
Kind regards,

Niek Otten
Microsoft MVP - Excel
=============================
' ===========================
' Ron Rosenfeld
' Copied form Google's Newsgroup Archives April 27, 2006

Function NWrkDays(StartDate As Date, EndDate As Date, _
Optional Holidays As Range = Nothing, _
Optional WeekendDay_1 As Integer = 1, _
Optional WeekendDay_2 As Integer = 7, _
Optional WeekendDay_3 As Integer = 0) As Long
' Sunday = 1; Monday = 2; ... Saturday = 7


'credits to Myrna


Dim i As Long
Dim Count As Long
Dim H As Variant
Dim w As Long
Dim SD As Date, ED As Date
Dim DoHolidays As Boolean
Dim NegCount As Boolean


DoHolidays = Not (Holidays Is Nothing)


SD = StartDate: ED = EndDate
If SD ED Then
SD = EndDate: ED = StartDate
NegCount = True
End If


w = Weekday(SD - 1)
For i = SD To ED
Count = Count + 1
w = (w Mod 7) + 1
Select Case w
Case WeekendDay_1, WeekendDay_2, WeekendDay_3
Count = Count - 1
Case Else
If DoHolidays Then
If IsNumeric(Application.Match(i, Holidays, 0)) Then _
Count = Count - 1
End If
End Select
Next i
If NegCount = True Then Count = -Count
NWrkDays = Count
End Function


Function WrkDay(StartDate As Date, ByVal NumDays As Long, _
Optional Holidays As Range = Nothing, _
Optional WeekendDay_1 As Integer = 1, _
Optional WeekendDay_2 As Integer = 7, _
Optional WeekendDay_3 As Integer = 0) As Date


' Sunday = 1; Monday = 2; ... Saturday = 7


Dim i As Long
Dim TempDate As Date
Dim Stp As Integer
Dim NonWrkDays As Long
Dim temp As Long, SD As Date, ED As Date


Stp = Sgn(NumDays)


'Add NumDays
TempDate = StartDate + NumDays


'Add Non-Workdays


Do While Abs(NumDays) < temp
SD = Application.WorksheetFunction.Min(StartDate + Stp, TempDate)
ED = Application.WorksheetFunction.Max(StartDate + Stp, TempDate)


temp = NWrkDays(SD, ED, Holidays, WeekendDay_1, WeekendDay_2, WeekendDay_3)
TempDate = TempDate + NumDays - Stp * (temp)
Loop


WrkDay = TempDate
End Function
' ==========================

"George Ray" <George wrote in message ...
|I am looking for an equivalent to the WORKDAY() function that does not need
| the analysis toolpak. I send a spreadsheet that uses it extensively, to many
| people, many of whom do not have the toolpak set up. I think there is an
| equivalent that uses SUMPRODUCT(), but I cannot find it. I have a table of
| holidays I can use, and I can set up a table of weekends if I need to.

WORKDAY() Function Equivalent with SUMPRODUCT()
 

A function work-around

=start_date+SIGN(days)*SMALL(IF((WEEKDAY(start_dat e+SIGN(days)*(ROW(INDIRECT
("1:"&ABS(days)*10))))={2,3,4,5,6})*
ISNA(MATCH(start_date+SIGN(days)*(ROW(INDIRECT("1: "&ABS(days)*10))),holidays
,0)),ROW(INDIRECT("1:"&ABS(days)*10))),ABS(days))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"George Ray" <George wrote in message
...
I am looking for an equivalent to the WORKDAY() function that does not
need
the analysis toolpak. I send a spreadsheet that uses it extensively, to
many
people, many of whom do not have the toolpak set up. I think there is an
equivalent that uses SUMPRODUCT(), but I cannot find it. I have a table of
holidays I can use, and I can set up a table of weekends if I need to.

WORKDAY() Function Equivalent with SUMPRODUCT()
 

should have mentioned that it is an array formula, it should be committed
with Ctrl-Shift-Enter, not

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"George Ray" <George wrote in message
...
I am looking for an equivalent to the WORKDAY() function that does not
need
the analysis toolpak. I send a spreadsheet that uses it extensively, to
many
people, many of whom do not have the toolpak set up. I think there is an
equivalent that uses SUMPRODUCT(), but I cannot find it. I have a table of
holidays I can use, and I can set up a table ofjust Enter. weekends if I
need to.

WORKDAY() Function Equivalent with SUMPRODUCT()
 

Try the WORKDAY replacement here. This works whether you want future dates or
past dates.

http://www.dicks-blog.com/archives/2...-addin-part-2/

If you want a slightly simpler formula where you have a list of all weekend
and holiday dates (P2:P1000) you could try this formula for future dates only

=SMALL(IF(ISNA(MATCH(ROW(INDIRECT("1:"&B2*10))+A2, P2:P1000,0)),ROW(INDIRECT("1:"&B2*10))+A2),B2)

confirmed with CTRL+SHIFT+ENTER

where A2 is start date and B2 number of workdays to add

note: assumes you will not have more than 9 sonsecutive holiday/weekend days





"George Ray" wrote:

I am looking for an equivalent to the WORKDAY() function that does not need
the analysis toolpak. I send a spreadsheet that uses it extensively, to many
people, many of whom do not have the toolpak set up. I think there is an
equivalent that uses SUMPRODUCT(), but I cannot find it. I have a table of
holidays I can use, and I can set up a table of weekends if I need to.