weeknum challenge

[computerblind]

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

 

[AlphaFrog]

It seemed to work for me.

Cell A1 has today's date (July 22, 2010). The formula returned the value 7
=AP_and_Week(A1,"AP")

What's not working?

 

[barry houdini]

How do you define the first week of January? Does that begin on 1st Jan, 1st Monday in Jan, 1st Sunday in Jan....or something else?

Given your weeks per period there will only be 52 in a year. In some years (roughly 1 in 6) you'll need to have a week 53, otherwise eventually your year will start in July

If you define the start of the year (week 1) as something like "1st Sunday in the year", then that will effectively be automatic, in some years AP12 will be 5 weeks long

 

[computerblind]

Hi AlphaFrog,

yes it is broken up into 52 weeks starting on the 1st sunday of the year, this year it starts on 3rd january.

 

[barry houdini]

I assume then that any date before the first Sunday, e.g. 1st Jan 2010, would therefore be in AP12 (from 2009)?

If you have a date in A2 this formula in B2 will give you the relevant period

="AP"&TEXT(MATCH((A2-WEEKDAY(A2)-DATE(YEAR(A2),1,-7))/7,{0,1,6,10,14,19,23,27,32,36,40,45,49})-1,"0;0;12")

Note that as I said previously some years will have 53 weeks, for example the 1st Sunday of 2012 is 1st Jan while the first Sunday of 2013 is 6th Jan, those dates are 53 weeks apart. In that case the above formula acts as if AP12 is 5 weeks long, i.e. all dates from 2nd Dec 2012 to 5th January 2013 inclusive are in AP12

 

[computerblind]

barry houdini, you are a genius I will give it a bit more trialling on some large spread sheets but it looks just like what I am after I thought that it would come from an "IF" statment but I guess there is more than one way to skin a cat.

Thanks for your help hope you enjoyed my challenge.

 

[computerblind]

Hi alpha frog,

How did you get the original working did you do it in the macro programming?

 

[AlphaFrog]

• I copied your original code to a VBA module.
• I didn't change a thing in your code.
• I put the formula in a cell. That's it.

Cell A1 has today's date (July 22, 2010). The formula returned the value 7
=AP_and_Week(A1,"AP")

I tested it again and it still works.

If you could give some details on what you mean by; "but it doesnt work", that would be a huge help. Do you get the wrong value? Do you get an error in the code or on the worksheet? Do you get nothing?