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
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

AlphaFrog

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

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,055
Messages
5,509,053
Members
408,701
Latest member
daz457

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top