#### computerblind

##### New Member

- Joined
- May 22, 2010

- Messages
- 5

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