#### Darren Bartrup

##### Well-known Member

- Joined
- Mar 13, 2006

- Messages
- 1,296

- Office Version
- 365

- Platform
- Windows

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:

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.