Returning a maximum week number based on a date.

[Darren Bartrup]

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

=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:
http://www.mrexcel.com/forum/excel-questions/803943-setting-up-staff-rotation-spreadsheet.html
(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.

 

[Darren Bartrup]

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

=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.

 

[chetanmehra1989]

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

=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

 

[Darren Bartrup]

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.....

=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.

 

[Darren Bartrup]

=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.

 

[chetanmehra1989]

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

 

[Darren Bartrup]

Hi chetanmehra,

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

=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