Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I set up a 6 day work week in Excel?
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...)? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I set up a 6 day work week in Excel?
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I set up a 6 day work week in Excel?
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)))) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=570566 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I set up a 6 day work week in Excel?
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...)? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I set up a 6 day work week in Excel?
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I set up a 6 day work week in Excel?
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... -- Bearacade ------------------------------------------------------------------------ Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016 View this thread: http://www.excelforum.com/showthread...hreadid=570566 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I set up a 6 day work week in Excel?
rrichter Wrote: ....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(A 1+{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 -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=570566 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I set up a 6 day work week in Excel?
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...)? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I set up a 6 day work week in Excel?
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... -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=570566 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I set up a 6 day work week in Excel?
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...)? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
POI doesn't work with Excel 97 | Excel Discussion (Misc queries) | |||
looking for Excel VBA work | Excel Discussion (Misc queries) | |||
Macro to copy cells to rows below | Excel Discussion (Misc queries) | |||
Macro to insert copied cells | Excel Discussion (Misc queries) | |||
Some Excel links don't work | Excel Discussion (Misc queries) |