Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,440
Default 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.


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default 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.



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default 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.


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 174
Default 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.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Workday function RUSH2CROCHET Excel Discussion (Misc queries) 4 May 17th 06 07:34 PM
numerical integration integreat Excel Discussion (Misc queries) 4 May 12th 06 02:40 AM
Custom functions calculating time arguments Help Desperate Bill_De Excel Worksheet Functions 12 April 25th 06 02:22 AM
SUMIF Function Inside SUMPRODUCT Function Abdul Waheed Excel Worksheet Functions 17 September 19th 05 04:24 PM
Excel option to store trendline's coefficients in cells for use Miguel Saldana Charts and Charting in Excel 9 June 20th 05 08:45 PM


All times are GMT +1. The time now is 07:04 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"