Calculating week number without WEEKNUM

[TommyMc]

We have a fiscal calendar which starts Oct 1. I would like to display the proper week numbers. I worked out a formula which seems to work (except for week 53) but it would be better if I didn't have to rely on other users having the Analysis Toolpak installed. My date is located in '3930!I4' and this is the formula that works with the toolpak:
=IF(WEEKNUM('3930'!I4)>=40,WEEKNUM('3930'!I4)-39,WEEKNUM('3930'!I4)+13)

I searched and found an alternate formula to replace WEEKNUM:
=INT(('3930'!I4-DATE(YEAR('3930'!I4),1,1))/7)+1
but when I substitute it for WEEKNUM in my formula, it doesn't work:
=IF(INT(('3930'!I4-DATE(YEAR('3930'!I4),1,1))/7)+1*('3930'!I4)>=40,INT(('3930'!I4-DATE(YEAR('3930'!I4),1,1))/7)+1*('3930'!I4)-40,INT(('3930'!I4-DATE(YEAR('3930'!I4),1,1))/7)+1*('3930'!I4)+14)
Returns the number 39723 rather than (week) 1
Can anybody help adjust my formula? Thanks

 

[sailepaty]

Maybe this,

=INT(('3930'!I4-DATE(2008,10,1))/7)+1

Regards

 

[TommyMc]

Thanks for the reply, and taking the time to look at this, I appreciate it. Your solution works for this fiscal year, which is a start. At least I'll only have to update it once a year. The formula I was using with WEEKNUM would roll over to a new FY every Oct 1. I was hoping to find a snippet to substitute for WEEKNUM where it appears in the formula. Your solution will buy me a year to fuss with it, thanks.

 

[sailepaty]

Does this work better?

=INT(('3930'!I4-DATE(YEAR('3930'!I4)-(MONTH('3930'!I4)<10),10,1))/7)+1

Regards

 

[TommyMc]

Perfect........you even got it to display week 53!! BTW, I messed around with that mile long "IF" formula I posted above, and got it to work by getting the parentheses all in the right place.....except it doesn't calculate week 53 like yours does. I was going to live with it, but YOUR solution is perfect. Elegant, and thanks for your help.

 

[barry houdini]

I searched and found an alternate formula to replace WEEKNUM:
=INT(('3930'!I4-DATE(YEAR('3930'!I4),1,1))/7)+1

That formula doesn't do the same as WEEKNUM. With WEEKNUM the 1st January is always in week 1, week 2 starts on the first Sunday after that. This means that in some years week 1 has only 1 day (and if that's a leap year then there'll also be a week 54, also with 1 day).

The above formula always makes 1st to 7th January week 1 and, similarly Sailepaty's suggestion makes 1st to 7th October week 1.

That might be entirely acceptable to you, especially if you are always looking at a single day in each week, but if you are looking at individual dates then you can find that you'll get different week numbers for the same week, e.g. yesterday (14th Oct) is week 2 given that formula but today is in week 3.

If you want the weeks to work like WEEKNUM, i.e. 1st October is always in week 1 but week 2 starts on the following Sunday then you can use this formula

=INT(('3930'!I4-WEEKDAY('3930'!I4)-DATE(YEAR('3930'!I4)-(MONTH('3930'!I4)<10),10,1))/7)+2

 

[sailepaty]

Thanks Barry for the explanation.

 

[MKB]

I have a similar problem to this. I can't use weeknum, when I enter a date I need a week number to appear in another cell - the week number is defined by the business, start of the week is always a Sunday, and there's not always a week 53.

So this year it should go:

06-Dec-08 49
07-Dec-08 50
08-Dec-08 50
09-Dec-08 50
10-Dec-08 50
11-Dec-08 50
12-Dec-08 50
13-Dec-08 50
14-Dec-08 51
15-Dec-08 51
16-Dec-08 51
17-Dec-08 51
18-Dec-08 51
19-Dec-08 51
20-Dec-08 51
21-Dec-08 52
22-Dec-08 52
23-Dec-08 52
24-Dec-08 52
25-Dec-08 52
26-Dec-08 52
27-Dec-08 52
28-Dec-08 1
29-Dec-08 1
30-Dec-08 1
31-Dec-08 1
01-Jan-09 1
02-Jan-09 1
03-Jan-09 1
04-Jan-09 2
05-Jan-09 2
06-Jan-09 2
07-Jan-09 2
08-Jan-09 2
09-Jan-09 2
10-Jan-09 2
11-Jan-09 3
12-Jan-09 3
13-Jan-09 3
14-Jan-09 3
15-Jan-09 3
16-Jan-09 3
17-Jan-09 3

The formula I have been using this year is:

=TRUNC(((B4-DATE(YEAR(B4),1,0))+8)/7)

Which works great, however as we go past 28th Dec it all goes wrong. What should be week 1, becomes week 53 up till end of Dec, then 1st Jan to 5th Jan as week 1, then 6th Jan as week 2 - by this time it's all gone wrong by week number and it's out of sync.

I tried a little IF function replacing 53 with 1 but this doesn't work. I googled and found this:

=1+INT((L4-DATE(YEAR(L4+4-WEEKDAY(L4+6)),1,5)+
WEEKDAY(DATE(YEAR(L4+4-WEEKDAY(L4+6)),1,3)))/7)

(where L4 is the date). This works brilliantly, but is a day out of sync - it treats the Monday as the start of the week, not the Sunday. It doesn't go into week 53 which is great, and starts week 1 in December, only this week starting on a Monday throws it out. I can't figure out what to change to get this to re-sync, without it throwing in a week 53, or a week zero. For reference, the formula came from here: http://www.cpearson.com/excel/weeknum.htm

 

[barry houdini]

.....I googled and found this:

=1+INT((L4-DATE(YEAR(L4+4-WEEKDAY(L4+6)),1,5)+
WEEKDAY(DATE(YEAR(L4+4-WEEKDAY(L4+6)),1,3)))/7)

Hello MKB,

That's the formula for ISO week numbers where all weeks have 7 days, weeks start on Mondays and week 1 always starts on the first Monday on or after 29th December.

How do you define your weeks?

Assuming, similar to ISO weeks, that weeks always have 7 days but your week 1 starts on the first Sunday on or after 28th December then this formula will give the week numbers. There will still be a week 53 in some years, e.g. in 2009

=INT((L4-WEEKDAY(L4)-DATE(YEAR(L4+5-WEEKDAY(L4)),1,3))/7)+2

 

[MKB]

That works a treat thanks, will this still be applicable after this year or will it go out of sync again? ie after week 53 in 2009?