Custom Week Numbers

**ianamck** · 07-05-2013, 07:52 AM

I know this subject has been covered a number of time and it seems daddylonglegs has been the hero in finding the solution. The problem I am trying to resolve with the simplest of formulas is a custom week number

The fiscal year always begins on April 6 , and runs from April 6 of one year to April 5 of the next year.

April 6 will always be in week 1 regardless of the day of the week.

The week changeover (or start of a new week) is always on a Monday ; So if April 6 is a Saturday (as in 2013), April 6 and April 7 would be in week 1 ; week 2 would start on the Monday with April 8th.

If April 6 falls on a Monday , it will be in week 1 , and week 2 will start with April 13.

I tried adapting daddylonglegs previous solution =INT((A4-LOOKUP(A4,DATE(YEAR(A4)-{2,1,0},4,06)-WEEKDAY(DATE(YEAR(A4)-{2,1,0},4,06-2))+7))/7)+1 to no avail.

I have found some extremely complicated solution requiring a lot of pre-calculations but is there a simple solution just as neat as shown above????

**NoSparks** · 07-05-2013, 02:49 PM

Maybe something along the lines of this

D2=IF(MOD(INT((C2-(B2-WEEKDAY(B2,2)+1))/7),A2)=0,1,MOD(INT((C2-(B2-WEEKDAY(B2,2)+1))/7),A2)+1)

where

A2=53 (is weeks or part of weeks in year)
B2=4/6/2013 (is start of fiscal year)
C2 is date in question
D2 is the week in fiscal year of date in C2

**newdoverman** · 07-05-2013, 02:54 PM

This will give you the first Monday of the month for any given date. (Any date that I tested)

Formula:

Please Login or Register  to view this content.

For April, you should be able to apply the above to start the numbering at 1.

The week numbers for 2013/2014 April to March 31 are:

Monday, April 01, 2013 1
Monday, April 08, 2013 2
Monday, April 15, 2013 3
Monday, April 22, 2013 4
Monday, April 29, 2013 5
Monday, May 06, 2013 6
Monday, May 13, 2013 7
Monday, May 20, 2013 8
Monday, May 27, 2013 9
Monday, June 03, 2013 10
Monday, June 10, 2013 11
Monday, June 17, 2013 12
Monday, June 24, 2013 13
Monday, July 01, 2013 14
Monday, July 08, 2013 15
Monday, July 15, 2013 16
Monday, July 22, 2013 17
Monday, July 29, 2013 18
Monday, August 05, 2013 19
Monday, August 12, 2013 20
Monday, August 19, 2013 21
Monday, August 26, 2013 22
Monday, September 02, 2013 23
Monday, September 09, 2013 24
Monday, September 16, 2013 25
Monday, September 23, 2013 26
Monday, September 30, 2013 27
Monday, October 07, 2013 28
Monday, October 14, 2013 29
Monday, October 21, 2013 30
Monday, October 28, 2013 31
Monday, November 04, 2013 32
Monday, November 11, 2013 33
Monday, November 18, 2013 34
Monday, November 25, 2013 35
Monday, December 02, 2013 36
Monday, December 09, 2013 37
Monday, December 16, 2013 38
Monday, December 23, 2013 39
Monday, December 30, 2013 40
Monday, January 06, 2014 41
Monday, January 13, 2014 42
Monday, January 20, 2014 43
Monday, January 27, 2014 44
Monday, February 03, 2014 45
Monday, February 10, 2014 46
Monday, February 17, 2014 47
Monday, February 24, 2014 48
Monday, March 03, 2014 49
Monday, March 10, 2014 50
Monday, March 17, 2014 51
Monday, March 24, 2014 52
Monday, March 31, 2014 53

**ianamck** · 07-05-2013, 06:18 PM

@ newdoverman

You have fallen in to the same trap I did when trying to get this straight in my head.

April 6 will always be in week 1 , while April 1 till April 5 will be week 52 or week 53 , depending on the calculations ; since they are a part of the previous year.

The start of any week is on a Monday ; as April 6th is a Saturday this year , April 6 and April 7 will be in week 1 ; week 2 will start with April 8. If April 6 falls on a Monday , it will be in week 1 , and week 2 will start with April 13.

So unfortunately your calculation as neat as it is is not giving me the solution

**ianamck** · 07-05-2013, 06:28 PM

@ No Sparks

Seems your solution is sending me down the same track I have been down some pre-qualification or some pre-calculation. What I was hoping to find and still racking my tiny brain for is a neat formula that just tells me the week number for any given date. Why is this important to me. In essence I have a large amount of raw data that is brought into a spread sheet which includes a date field. With some simple formulas I can read that data in a master table where I can manipulate the data tp give me the results I need for the reports. Its important that the reports I use know which data belongs in which fiscal week to give the correct information (totals etc) for the powers that be. What I have always been trying to do is avoid having to mess to much with the raw data as it already has a date field a simple formula means I don't actually have to touch the raw data just calculate everything off the master table that reads all the information from the raw data.

Hope that make sense

**daddylonglegs** · 07-05-2013, 07:01 PM

What you are looking for is essentially WEEKNUM(Date,2) except that instead of a year start of 1st January you want a year start of 6th April - all other characteristics are the same.

For WEEKNUM(Date,2) you can use this formula to give the same result

=INT((A4-DATE(YEAR(A4),1,1)-WEEKDAY(A4-1))/7)+2

...for a date in A4, so where DATE(YEAR(A4),1,1) will give the 1st Jan date previous to A4, you want the 6th April Date previous to A4 which you can get with this formula

=DATE(YEAR(A4+270)-1,4,6)

...because 6th April is 270 days from the end of the year, so the formula you need for your week number is

=INT((A4-DATE(YEAR(A4+270)-1,4,6)-WEEKDAY(A4-1))/7)+2

Originally Posted by ianamck

.....while April 1 till April 5 will be week 52 or week 53 , depending on the calculations.....

As with WEEKNUM it's possible for the last day of the year to be in week 54 - that will only happen when 5th April is a Monday in a leap year so that will be approximately once every 28 years (the next one is 2032)

**ianamck** · 07-05-2013, 07:53 PM

daddylonglegs you are still my Date hero. I eventually found your similar post from this time last years but couldn't quite figure it completely out. The 270 days now fills that missing link in. I knew you would have the simple answer and make life a lot cleaner and simpler with a one hit formula. I tested your formula to 2050 and it works like a dream.

Many thanks for the solution. Problem SOLVED