computerblind
New Member
- Joined
- May 22, 2010
- Messages
- 5
Hi everyone I have a challenge,
why doesnt u tube have more week num examples?
I am trying to create spread sheet that when people put date in one column then another cell returns something called an "AP", which is basically first 5 weeks of january is "AP1" next 4 weeks after that is "AP2" and so on.
first 5 weeks = AP1
next 4 weeks =AP2
next 4 weeks =AP3
next 5 weeks =AP4
next 4 weeks =AP5
next 4 weeks =AP6
next 5 weeks =AP7
next 4 weeks =AP8
next 4 weeks =AP9
next 5 weeks =AP10
next 4 weeks =AP11
next 4 weeks =AP12
I have an example of vba programming but it doesnt work see here.
Function AP_and_Week(ByVal dStatDate As Date, ByVal stype_of_request As String) As Integer
Dim weekNo As Integer
weekNo = DatePart("ww", dStatDate, 1, 2)
If stype_of_request = "AP" Then
If weekNo > 48 Then
AP_and_Week = 12 ' if Weekno over 48 then AP = 12
ElseIf weekNo > 44 Then
AP_and_Week = 11 ' if Weekno over 44 and below 48 then AP = 11
ElseIf weekNo > 39 Then
AP_and_Week = 10 ' if Weekno over 39 and below 44 then AP = 10
ElseIf weekNo > 35 Then
AP_and_Week = 9 ' if Weekno over 35 and below 39 then AP = 9
ElseIf weekNo > 31 Then
AP_and_Week = 8 ' if Weekno over 31 and below 35 then AP = 8
ElseIf weekNo > 26 Then
AP_and_Week = 7 ' if Weekno over 26 and below 31 then AP = 7
ElseIf weekNo > 22 Then
AP_and_Week = 6 ' if Weekno over 22 and below 26 then AP = 6
ElseIf weekNo > 18 Then
AP_and_Week = 5 ' if Weekno over 18 and below 22 then AP = 5
ElseIf weekNo > 13 Then
AP_and_Week = 4 ' if Weekno over 13 and below 18 then AP = 4
ElseIf weekNo > 9 Then
AP_and_Week = 3 ' if Weekno over 9 and below 13 then AP = 3
ElseIf weekNo > 5 Then
AP_and_Week = 2 ' if Weekno over 5 and below 9 then AP = 2
Else
AP_and_Week = 1 ' If Weekno below 5 then AP = 1
End If
Else
AP_and_Week = weekNo ' Not looking for AP (stype_of_request NOT "AP") record week no.
End If
End Function
why doesnt u tube have more week num examples?
I am trying to create spread sheet that when people put date in one column then another cell returns something called an "AP", which is basically first 5 weeks of january is "AP1" next 4 weeks after that is "AP2" and so on.
first 5 weeks = AP1
next 4 weeks =AP2
next 4 weeks =AP3
next 5 weeks =AP4
next 4 weeks =AP5
next 4 weeks =AP6
next 5 weeks =AP7
next 4 weeks =AP8
next 4 weeks =AP9
next 5 weeks =AP10
next 4 weeks =AP11
next 4 weeks =AP12
I have an example of vba programming but it doesnt work see here.
Function AP_and_Week(ByVal dStatDate As Date, ByVal stype_of_request As String) As Integer
Dim weekNo As Integer
weekNo = DatePart("ww", dStatDate, 1, 2)
If stype_of_request = "AP" Then
If weekNo > 48 Then
AP_and_Week = 12 ' if Weekno over 48 then AP = 12
ElseIf weekNo > 44 Then
AP_and_Week = 11 ' if Weekno over 44 and below 48 then AP = 11
ElseIf weekNo > 39 Then
AP_and_Week = 10 ' if Weekno over 39 and below 44 then AP = 10
ElseIf weekNo > 35 Then
AP_and_Week = 9 ' if Weekno over 35 and below 39 then AP = 9
ElseIf weekNo > 31 Then
AP_and_Week = 8 ' if Weekno over 31 and below 35 then AP = 8
ElseIf weekNo > 26 Then
AP_and_Week = 7 ' if Weekno over 26 and below 31 then AP = 7
ElseIf weekNo > 22 Then
AP_and_Week = 6 ' if Weekno over 22 and below 26 then AP = 6
ElseIf weekNo > 18 Then
AP_and_Week = 5 ' if Weekno over 18 and below 22 then AP = 5
ElseIf weekNo > 13 Then
AP_and_Week = 4 ' if Weekno over 13 and below 18 then AP = 4
ElseIf weekNo > 9 Then
AP_and_Week = 3 ' if Weekno over 9 and below 13 then AP = 3
ElseIf weekNo > 5 Then
AP_and_Week = 2 ' if Weekno over 5 and below 9 then AP = 2
Else
AP_and_Week = 1 ' If Weekno below 5 then AP = 1
End If
Else
AP_and_Week = weekNo ' Not looking for AP (stype_of_request NOT "AP") record week no.
End If
End Function