Greetings,

need help on setting up a worksheet, that makes the week end on Friday, and
starts on Saturday.

currently, weeknum only helps with Mondays & Sundays.

eg. 1st Apr (tuesday) -- 5th Apr(saturday) = week 14 (using weeknum(A1,1)

i'm trying to make 1st Apr (tuesday)-- 4th Apr(friday) = week 14, while 5th
Apr(sat) -- 11th Apr(Friday) = week 15.


TIA.

Here is a UDF from Ron Rosenfeld.

Copy the text of the function
ALT+F11 will bring you to the Visual Basic Editor
InsertModule
Paste the text of the function into the Module
ALT+F11 brings you back to the worksheet
You van now use the function from the worksheet

--
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
' ==========================



"tze" wrote in message ...
| Greetings,
|
| need help on setting up a worksheet, that makes the week end on Friday, and
| starts on Saturday.
|
| currently, weeknum only helps with Mondays & Sundays.
|
| eg. 1st Apr (tuesday) -- 5th Apr(saturday) = week 14 (using weeknum(A1,1)
|
| i'm trying to make 1st Apr (tuesday)-- 4th Apr(friday) = week 14, while 5th
| Apr(sat) -- 11th Apr(Friday) = week 15.
|
|
| TIA.
|

Hi

Here is an UDF I wrote a cople of years ago. Tehere are some additional UDFs
for array operations you have to copy into VBA module along with
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 parameter Weekends is set to FALSE.

' The parameter WeekStart determines, how are determined weekends in 4th
parameter
' Allowed 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 the is the only 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 )

Try this:

=WEEKNUM(A1)+(WEEKDAY(A1)=7)


"tze" wrote:

Greetings,

need help on setting up a worksheet, that makes the week end on Friday, and
starts on Saturday.

currently, weeknum only helps with Mondays & Sundays.

eg. 1st Apr (tuesday) -- 5th Apr(saturday) = week 14 (using weeknum(A1,1)

i'm trying to make 1st Apr (tuesday)-- 4th Apr(friday) = week 14, while 5th
Apr(sat) -- 11th Apr(Friday) = week 15.


TIA.

=WEEKNUM(A1+1) ?
--
David Biddulph

"tze" wrote in message
...
Greetings,

need help on setting up a worksheet, that makes the week end on Friday,
and
starts on Saturday.

currently, weeknum only helps with Mondays & Sundays.

eg. 1st Apr (tuesday) -- 5th Apr(saturday) = week 14 (using weeknum(A1,1)

i'm trying to make 1st Apr (tuesday)-- 4th Apr(friday) = week 14, while
5th
Apr(sat) -- 11th Apr(Friday) = week 15.


TIA.