Hi

To create UDF (User Defined Function) ENCHWORKDAY():

With your workbook opened and active, press Alt+F11 - this opens VBA Editor.
As you likely don't any VBA code inserted before this, select from Edit menu
of VBA editor InsertModule. In VBA Project subwindow in leftmost upper
corner of VBA editor, an object Module1 does appear (when you did have some
modules before, the number of module may be different).
Doubble-click on newly created module (Module1). Paste the code I provided
into code window, which did appear in rightmost upper section of VBA editor.
NB! You have to edit the code, to repair code rows distorted when posted
(some code rows will be broken to several parts)
Close the VBA editor.
Now you can use the UDF as any other Excel function - but in this workbook
only. (There are means to make an UDF available in all workbooks opened in
your comp, but for start you don't need this.) Simply enter into cell
formula like
=ENCHWORKDAY(A1,B1,Holidays!A1:A20)
, or
=ENCHWORKDAY(A1,B1,{DATE(2006,1,1);DATE(2006,5,1); DATE(2006,24,12)},{1;7},1)
etc. Read comments in function header to get some information about function
parameters (comments are rows started with an apostrophe).


Arvi Laanemets


"rrichter" wrote in message
...
Hello Arvi,

I appreciate the information that you gave, but I have no clue what a UDF
is
or how to input the information you supplied. I am a beginner and am a
bit
intimidated by your answer.

Is it possible that you can break this up into a more simplified
direction?
Again, I am trying to create a string of consecutive dates (please see my
post to Bearacade 8-11-06) that would exclude Sundays, and a holiday
array.

Please advise. Thank you for your time and patience. I greatly
appreciate
your help. rrichter...

"Arvi Laanemets" wrote:

Hi

You can try this UDF (other functions are used by EnchWorkdaysN)

*****
Option Base 1



Public Function EnchWorkdaysN(StartDate As Date, _
EndDate As Date, _
Optional Holidays As Variant = Nothing,
_
Optional Weekends As Variant = Nothing,
_
Optional WeekStart As Integer = 1)


Dim arrayH As Variant, arrayW As Variant
Dim di As Date, dn As Date, dx As Date

' The result doesn't depend on order of values of first 2
parameters.

' When parameter Holidays is omitted, or Null, or not a positive
numeric
(date) value,
' or not an array or cell range with numeric values, then no
holidays
' are left out from day's count.

' When parameter Weekends is omitted, or Null, or not a numeric
value
=1 and <8,
' or not an array or cell range with at least one numeric value
between
=1 and <8,
' then 1 and 7 (Saturday and Sunday) are set for Weekend default
walues,
' and according weekdays are left out from day's count.
' No weekends are left out from day's count (7-workday week) only
then,
' when fourth parameter - Weekends - is set to FALSE.

' The parameter WeekStart determines, how are determined weekends in
4th
parameter
' Processed values for parameter WeekStart are integers 1 to 7.
' The number 1 indicates Sunday as 1st day of week,
' the number 2 indicates Monday as first day of week, etc.
' When the parameter WeekStart is not between 1 and 7, then
WeekStart =
(Abs(WeekStart) Mod 7)+1


' Initialize ArrayH
If TypeName(Holidays) = "Variant()" Then
ReDim arrayH(1 To UBound(Holidays)) As Variant
For i = 1 To UBound(Holidays)
arrayH(i) = IIf(VarType(Holidays(i, 1)) 0 And
VarType(Holidays(i, 1)) < 8, Holidays(i, 1), Null)
arrayH(i) = IIf(arrayH(i) < 0, Null, arrayH(i))
Next i
ElseIf (VarType(Holidays) = 8192 And VarType(Holidays) <= 8199) Or
_
VarType(Holidays) = 8204 Then
ReDim arrayH(1 To UBound(Holidays.Value)) As Variant
For i = 1 To UBound(Holidays.Value)
arrayH(i) = IIf(VarType(Holidays(i)) 0 And
VarType(Holidays(i)) < 8, Holidays(i), Null)
arrayH(i) = IIf(arrayH(i) < 0, Null, arrayH(i))
Next i
ElseIf VarType(Holidays) < 8 Then
ReDim arrayH(1) As Variant
arrayH(1) = Holidays
arrayH(1) = IIf(arrayH(1) < 0, Null, arrayH(1))
Else
ReDim arrayH(1) As Variant
arrayH(1) = Null
End If
' Sort arrayH
SelectionSort arrayH
' Replace non-integer values with integers
SelectionToInteger arrayH
' Remove double entries and empty elements
SelectionUnique arrayH


' Initialize arrayW
If VarType(Weekends) < 11 Then
If TypeName(Weekends) = "Nothing" Then
ReDim arrayW(1 To 2) As Variant
arrayW(1) = 1
arrayW(2) = 7
ElseIf TypeName(Weekends) = "Variant()" Then
ReDim arrayW(1 To UBound(Weekends)) As Variant
For i = 1 To UBound(Weekends)
If UBound(Weekends) = 1 Then
arrayW(i) = IIf(VarType(Weekends(i)) 0 And
VarType(Weekends(i)) < 8, ((Abs(Weekends(i)) + 12 + WeekStart) Mod 7) +
1,
Null)
Else
arrayW(i) = IIf(VarType(Weekends(i, 1)) 0 And
VarType(Weekends(i, 1)) < 8, ((Abs(Weekends(i, 1)) + 12 + WeekStart) Mod
7)
+ 1, Null)
End If
arrayW(i) = IIf(arrayW(i) < 1 Or arrayW(i) = 8, Null,
arrayW(i))
Next i
ElseIf VarType(Weekends) = 8192 And VarType(Weekends) <= 8199
Or _
VarType(Weekends) = 8204 Then
ReDim arrayW(1 To UBound(Weekends.Value)) As Variant
For i = 1 To UBound(Weekends.Value)
arrayW(i) = IIf(VarType(Weekends(i)) 0 And
VarType(Weekends(i)) < 8, ((Abs(Weekends(i)) + 12 + WeekStart) Mod 7) +
1,
Null)
arrayW(i) = IIf(arrayW(i) < 1 Or arrayW(i) = 8, Null,
arrayW(i))
Next i
ElseIf (Int(Weekends) = 1 And Int(Weekends) < 8) Then
ReDim arrayW(1) As Variant
arrayW(1) = ((Abs(Weekends) + 12 + WeekStart) Mod 7) + 1
arrayW(1) = IIf(arrayW(1) < 1 Or arrayW(1) = 8, Null,
arrayW(1))
Else
ReDim arrayW(1 To 2) As Variant
arrayW(1) = 1
arrayW(2) = 7
End If
' Sort arrayW
SelectionSort arrayW
' Replace non-integer values with integers
SelectionToInteger arrayW
' Remove double entries and empty elements
SelectionUnique arrayW, False
Else
' Set 1st element to 0 for 7-workday week
ReDim arrayW(1) As Variant
arrayW(1) = IIf(Weekends = False, 0, Null)
End If

' When empty array, insert default values
If arrayW(1) = Null Then
ReDim arrayW(1 To 2, 1) As Variant
arrayW(1) = 1
arrayW(2) = 7
End If

' Calculate the number of workdays in date interval determined by
StartDay and EndDay
EnchWorkdaysN = 0
di = Application.WorksheetFunction.Min(StartDate, EndDate)
dn = Application.WorksheetFunction.Max(StartDate, EndDate)
dx = di
Do While dx <= dn
x = False
i = 1
Do While x = False And i <= UBound(arrayH) And
TypeName(arrayH(1))
< "Null"
x = (dx = arrayH(i))
i = i + 1
Loop
i = 1
Do While x = False And i <= UBound(arrayW) And arrayW(1) < 0
x = (Weekday(dx) = arrayW(i))
i = i + 1
Loop
If Not (x) Then EnchWorkdaysN = EnchWorkdaysN + 1
dx = dx + 1
Loop
End Function
Function SelectionSort(TempArray As Variant)
Dim MaxVal As Variant
Dim MaxIndex As Integer
Dim i, j As Integer

' The function sorts all entries in 1-dimensional array,
' it's a function provided in Microsoft KB article 133135

' Step through the elements in the array starting with the
' last element in the array.
For i = UBound(TempArray) To 1 Step -1

' Set MaxVal to the element in the array and save the
' index of this element as MaxIndex.
MaxVal = TempArray(i)
MaxIndex = i

' Loop through the remaining elements to see if any is
' larger than MaxVal. If it is then set this element
' to be the new MaxVal.
For j = 1 To i
If TempArray(j) MaxVal Then
MaxVal = TempArray(j)
MaxIndex = j
End If
Next j

' If the index of the largest element is not i, then
' exchange this element with element i.
If MaxIndex < i Then
TempArray(MaxIndex) = TempArray(i)
TempArray(i) = MaxVal
End If
Next i

End Function

Function SelectionUnique(TempArray As Variant, Optional AllowZeros As
Boolean = True)
Dim MaxVal, TempArray2() As Variant
Dim MaxIndex As Integer
Dim i, j As Integer

' The function is meant to work with ordered arrays
' and removes all double entries and Null values
' (Except when there is a single value, and it is Null).
' Optional argument determines, how 0 values are processed

' Initialize
j = 1
ReDim TempArray2(1 To j) As Variant
TempArray2(1) = Null

' Step through the elements in the array starting with the
' first element in the array.
For i = 1 To UBound(TempArray) Step 1

If IsNull(TempArray(i)) Or _
IsEmpty(TempArray(i)) Or _
(TempArray(i) = 0 And AllowZeros = False) Then
Else
' Redim TempArray2 and add an element
ReDim Preserve TempArray2(1 To j) As Variant
TempArray2(j) = TempArray(i)
j = j + 1

' Set CurrVal to the element in the array
currval = TempArray(i)

' Cycle through next elements until value changes
k = 0
If i < UBound(TempArray) Then
Do While TempArray(i + k + 1) = currval
k = k + 1
If i + k UBound(TempArray) Then Exit Do
Loop
End If
i = Application.WorksheetFunction.Max(i, i + k - 1)
End If

Next i

' Write the passed array over
TempArray = TempArray2

End Function

Function SelectionToInteger(TempArray As Variant)
Dim i As Integer

' The function cuts off decimal part from all non-empty elements of
array

' Step through the elements in the array starting with the
' first element in the array.
For i = 1 To UBound(TempArray) Step 1

If IsNull(TempArray(i)) Then
Else
' Replace array element with it's integer value
TempArray(i) = Int(TempArray(i))
End If

Next i

End Function
*****


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


"rrichter" wrote in message
...
I normally use the WORKDAY function in order to create an involved
scheduling
program. However, I have no idea how to create a formula that would
count
Saturdays as a workday. I usually type in one date and then the dates
kick
out to an end date several months later. How do I create a formula to
include Saturdays on a daily projection (e.g. A1=8/12/06, A2=8/14/06,
A3=8/15/06,etc...)?