Find Week in Quarter from Date

You can get Quarter number with:
=LOOKUP(MONTH(A2),{1,4,7,10},{"Q1","Q2","Q3","Q4"})

But resetting week numbers can be attained with some tweaking of the WEEKNUM but may be a bit inaccurate:

Please Login or Register  to view this content.

Ron

EDIT: Week # formula revised

Hello Ron, your weeknum formula gives me 2 for 1st July 2011 - I assume that 1st July would always be in week 1

If week numbers work like WEEKNUM does at the start of the year, i.e. the first day of the quarter is always in week 1, week 2 starts on the next Sunday then this formula should give the week number of the quarter

=INT((A2-WEEKDAY(A2)-LOOKUP(A2,DATE(YEAR(A2),{1,4,7,10},1)))/7)+2

.....or this formula also gives the same result.....

=WEEKNUM(A2)-WEEKNUM(LOOKUP(A2,DATE(YEAR(A2),{1,4,7,10},1)))+1

Yep, your second formula was something I was trying to get at...

EDIT: The trick is where the OP wants 1st Jul 2011 in week 14 of Q2 and not week 1 of Q3. Cause if his wish is that week 1 starts on 3rd Jul 2011, then he'll have to live with your formula cause I don't think it can get better than that....

If you see a short sweet formula like that written by someone with full skills bar - go with that.... I think by now you've exhausted every combination date formulas can be used in. I haven't seen DKO for a while, is he still active?

Ron

Originally Posted by ron2k_1

.....I haven't seen DKO for a while, is he still active?....

I believe he got a job, now that takes up all the time he use to spend here...

The formula I suggested works following the logic I outlined in my earlier post, i.e.

the first day of the quarter is always in week 1, week 2 starts on the next Sunday

Using that logic 1st July 2011 is in week 1, week 2 then starts on the following Sunday, 3rd July 2011 so week 13 is 18th - 24th September and the remainder of the period (including 27th September) is in week 14. Most quarters will have 14 weeks - quarters 3 and 4 have 92 days each which is 13 weeks and one day - how can you not have a week 14 unless some weeks are longer than 7 days (or you have a week zero)?

If you can explain your logic then I'm sure the formula can be adjusted to suit

Hi Daddylonglegs// your formula is what i was looking for. really appreciate.

it works fine if the months of quarter are in the same year. if quarter is from last year to this year, it doesn't works

for example,
Q1 in my company starts from Nov.

Q1 : 2011-11 ~ 2012-01
Q2 : 2012-02 ~ 2012-04
Q3 : 2012-05 ~ 2012-07
Q4 : 2012-08 ~ 2012-10

your formula works perfectly for Q2,Q3,Q4. but not for Q1.

is there any way for this case??!

plus, how can i adjust when the week starts?
which means, week starts from sunday in this formula but if i need to change it to thursday?

realbum,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.