Looking for Fomula to display week in Current Quarter

Hi,

I am looking to have a permanent formula within an existing Excel Spreadsheet that will autocalculate the current Network Week in the current Quarter (e.g today 11th July = 2, we are now in the 2nd Network week of the 3rd Quarter). So yes for this purpose the Sunday (1st July) does not count as a week. Hope that sounds right?

Where i've got to so far (self taught on excel and whilst i have learnt quite a number of formula's i am far from experienced) is as follows:-

Cell A1 - i have the current date using =today()
Cell A2 - I have the week number based on the date in A1 using =weeknum(A1,1)
Finally in cell A3 - I have a rather longwinded IF AND formula to return the week i am after - using - =IF(A2<=13,A2,IF(AND(A2>13,A2<=26),A2-13,IF(AND(A2>26,A2<=39),A2-26,IF(AND(A2>39,A2<=52),A2-39))))

Whilst this appears to be working so far i'm not exactly 100% confident that its correct. Could someone see if it appears right and is there a quicker/better way of calculating what i want?

1st time poster and new member, so be gentle

You could do it directly in A2 using this formula:

=MOD(WEEKNUM(A1,1)-1,13)+1

MOD gives you the remainder after division (in this case division by 13), so if the actual weeknum is 28, then weeknum-1 gives 27, dividing this by 13 gives a remainder of 1, and then if you add 1 back on you will get 2. The reason for subtracting 1 and then adding it back in is that the remainders after division could be 0, 1, 2 etc. up to 12, and week zero doesn't mean anything.

Of course, if you want the result in A3 as you are trying to get it now, you can just use:

=MOD(A2-1,13)+1

Hope this helps.

Pete

One option for cell A3:
=IF(MOD(A2,13),IF(A2<=13*4,MOD(A2,13)),13)
//Ola

i really appreciate your help and recommendations. i will try those today....

thanks again

Dave

Worked a treat - Thanks