How do I set up a 6 day work week in Excel?

[rrichter]

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...)?

[Bearacade]

I am not sure what you are asking.. but try this..

I put 08/12/06 in A1, and put this in A2:

=IF(WEEKDAY(A1)=7, A1+2, A1+1)

Then I fill dragged down.

Make sure your format the cells into date

[daddylonglegs]

If you want to add a number of days (B1) to a date (A1) excluding only Sundays

=A1-WEEKDAY(A1,3)+INT(7/6*(B1+MIN(5,WEEKDAY(A1,3))))

[Arvi Laanemets]

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<at>tarkon.ee )


"rrichter" <[email protected]> wrote in message
news:[email protected]...
>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...)?

[rrichter]

Hello Bearacade:

I normally would have a field in a workbook where every single work day
would be incorporated into an adjoining cell. Every cell representing a
consecutive work day. So for a five day work week I would normally use the
function =WORKDAY(startdate,1,{holiday array}) and it would like
=WORKDAY(A1,1,$G$5:$G$20) and then I would drag and fill from column (A1) to
column (EE1) and have a date in every cell that excluded weekends and
holidays.

I hoping to find a formula that would do the same kind of autofill that
would only exclude Sunday and holidays. Any help is greatly appreciated, I
am beginner at best.

Thank you,


> I am not sure what you are asking.. but try this..
>
> I put 08/12/06 in A1, and put this in A2:
>
> =IF(WEEKDAY(A1)=7, A1+2, A1+1)
>
> Then I fill dragged down.
>
> Make sure your format the cells into date
>
>
> --
> Bearacade
>
>
> ------------------------------------------------------------------------
> Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
> View this thread: http://www.excelforum.com/showthread...hreadid=570566
>
>

[Bearacade]

To do it automatically, you would probably have to try Arvi's function.

My formula will allows you to show a 6 day workweek. Without doing some SUPER crazy formula, it isn't really possible to take the holidays out... Sorry...

[daddylonglegs]

....for a five day work week I would normally use the
function =WORKDAY(startdate,1,{holiday array}) and it would like
=WORKDAY(A1,1,$G$5:$G$20) and then I would drag and fill from column (A1) to
column (EE1) and have a date in every cell that excluded weekends and
holidays.

Not particularly simple but you could try this formula instead

=MIN(IF((WEEKDAY(A1+{1,2,3,4,5})<>1)*(ISNA(MATCH(A1+{1,2,3,4,5},$G$5:$G$20,0))),A1+{1,2,3,4,5}))

The assumption is that you would never have more than 4 consecutive days which are not working days (i.e. holidays or Sundays). If this is not the case then extend the {1,2,3,4,5} accordingly

[rrichter]

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<at>tarkon.ee )
>
>
> "rrichter" <[email protected]> wrote in message
> news:[email protected]...
> >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...)?
>
>
>

[daddylonglegs]

Hi rrichter,

did you see my previous reply?

I don't know if you would prefer a formula solution. Perhaps try it and see what you think...

[Arvi Laanemets]

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 Insert>Module. 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" <[email protected]> wrote in message
news:[email protected]...
> 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<at>tarkon.ee )
> >
> >
> > "rrichter" <[email protected]> wrote in message
> > news:[email protected]...
> > >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...)?
> >
> >
> >

[PRDIGM47]

This is the formula I use to create a 6 day working week.

=IF(OR(A1="",B1=""),"",SUMPRODUCT(--(COUNTIF(Hols,ROW(INDIRECT(A1&":"&B1)))=0),--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),3)<6)))

I normal define a range called "Hols" either on the same sheet or a separate sheet which has the Federal Holidays.

I hope this helps!
My best regards,
PRDIGM47

[AliGW]

The last post before yours was 14 years ago, but thanks!