weeknum challenge

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
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,416
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

New Member
Joined
May 22, 2010
Messages
5
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825

ADVERTISEMENT

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

New Member
Joined
May 22, 2010
Messages
5
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.
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,416
  • 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?
 

Watch MrExcel Video

Forum statistics

Threads
1,132,819
Messages
5,655,468
Members
418,204
Latest member
ElizabethCorrin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top