Returning a maximum week number based on a date.

Darren Bartrup

Well-known Member
Afternoon all,

Sometimes I swear getting a subject title is harder than trying to figure the answer out.

I'm having fun with dates and week numbers today.
What I'm trying to do is create a rolling week number, with a maximum week number that may change.

e.g. The date 06/04/2014 is week number 1 and 13/04/2014 is week number 2.
If I set the maximum week number as 2 I'd like 20/04/2014 to be week number 1 and 27/04/2014 to be week number 2 and so on....

Likewise if I make the maximum week number to be 8 then 06/04/2014 is week number 1, and 25/05/2014 is week number 8, 01/06/2014 will be 1 and 20/07/2014 will be week 8.

The problem I'm having is all occurrences of the maximum week after the first is 0, rather than the maximum.

The formula I'm using is a cobbled together update of the formula I found on this thread: http://www.mrexcel.com/forum/excel-questions/202332-tax-week-number.html
Code:
``=IF(INT((E2-DATE(YEAR(E2+270),-8,-1))/7)>\$H\$1,MOD(INT((E2-DATE(YEAR(E2+270),-8,-1))/7)/\$H\$1,1)*\$H\$1,INT((E2-DATE(YEAR(E2+270),-8,-1))/7))``

E2 is the date and H1 is the maximum number of weeks I want.

I'm using the formula to help me solve the problem on this thread:
(the responder has given me a clue to how that thread could work - just want to get it right before updating the thread).

Does anyone have any hints on how to get the 0's to show the maximum number and how to generally improve the formula.

Many thanks in advance as allways.

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
It's amazing what a nights rest can do.....

Code:
``=MOD(INT((\$B36-DATE(YEAR(\$B36),1,1))/7)+1,MaxGroup)+1``

This will rotate the week number between 1 and the value of MaxGroup (with the date in cell B36) - well almost, it starts at 2 goes up to the value of MaxGroup and then places the 1. This is good enough for my purposes though.

it's amazing what a nights rest can do.....

Code:
``=mod(int((\$b36-date(year(\$b36),1,1))/7)+1,maxgroup)+1``

this will rotate the week number between 1 and the value of maxgroup (with the date in cell b36) - well almost, it starts at 2 goes up to the value of maxgroup and then places the 1. This is good enough for my purposes though.

=max(m:m)-weekday(max(m:m))+7

Was almost good enough for my purposes - it would count from 1 to MaxGroup, but occasionally at the start of the year it would muck up.

So.....
Code:
``=MOD(INT((A4-39083)/7)+1,MaxGroup)+1``

This counts the number of weeks since 1st January 2007 (the last year that started on a Monday) and then groups them into groups of 1 to the value of MaxGroup. It appears to be working for dates between 1st Jan 2007 and 14th March 2033.

=max(m:m)-weekday(max(m:m))+7

Sorry, chetanmehra - with my maximum dates as 14th March 2033 this returns 19th March 2033 for every date.

I want it to return 1, 2, 3, 4, 5, 6, 7, 1, 2, 3, 4, 5, 6, 7 if the maximum number of groups is 7.

Hi Darren,

Can U just tell me with the help of example

let take an example
-12-jul-2014 between 30-aug 2014 then week should be this one 30-Aug 2014
is i m right

Hi chetanmehra,

It's ok - I've figured it out. Thanks anyway.

Code:
``=MOD(SUM(INT((C\$9-39083)/7)+1,\$B10-1),MaxGroup)+1``

MaxGroup is the maximum number of groupings in the team - so for a team of 40 people there will be 10 groups of 4.
\$C9 is the Monday week commencing date.
\$B10 is the group number (1, 2, 3, ..... MaxGroup)

So 12th July 2014 would have a week commencing date of 7th July 2014:

• Group 1 would be 4
• Group 2 would be 5
• Group 3 would be 6
• ....
• Group 7 would be 10
• Group 8 would be 1
• Group 9 would be 2
• Group 10 would be 3

If the team is 20 people there will be 5 groups of 4.

• Group 1 would be 4
• Group 2 would be 5
• Group 3 would be 1
• Group 4 would be 2
• Group 5 would be 3

Replies
2
Views
163
Replies
1
Views
333
Replies
3
Views
218
Replies
1
Views
395
Replies
14
Views
445

1,203,514
Messages
6,055,836
Members
444,828
Latest member
StaffordStag

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.

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

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