How to use WEEKNUM for a calendar when the year turns?

Thank you both for your help! Adding 7 gets it right for almost all years.

For example, when entering december 2009 and calculating enough weeks ahead it correctly tags January 4th as the Monday in week 1. But entering January 2010 as start month, it tags January 3rd on Monday week 53, hence Jan 1st would be on Friday week 52?? A similar problem occured around 1999-2000.

Maybe I should start considering rebuilding the entire thing, doing the calculations in a completely different manner?

Try this formula in cell B3 to get the date for the Måndag of the week containing the first of the month without using the result in A3, i.e. the WEEKNUM :

=DATE($B1,MATCH(LEFT($A1,3),{"Jan";"Feb";"Mar";"Apr";"Maj";"Jun";"Jul";"Aug";"Sep";"Okt";"Nov";"Dec"},0),1)-WEEKDAY(DATE($B1,MATCH(LEFT($A1,3),{"Jan";"Feb";"Mar";"Apr";"Maj";"Jun";"Jul";"Aug";"Sep";"Okt";"Nov";"Dec"},0),1),3)+INT(SEARCH(LEFT(B4,2),"MåTiOnToFrLöSö")/2)

This formula calculates the date for the first of the month and then subtracts the WEEKDAY value for the first of the month, using Måndag = 0, Tisdag = 1, ..., Söndag = 6. And finally the day offset for your "nine days week" is added.

Drag the formula across to cell J3.

Then use the +7 method like previously suggested:

In cell B15: =B3+7 and drag it to the right

In cell B27: =B15+7 and drag it to the right

and so on.

Hope this helps / Lars-Åke

Edit: P.S. A slightly shorter alternative to the

INT(SEARCH(LEFT(B4,2),"MåTiOnToFrLöSö")/2

part of the formula is

MID("012345566",COLUMNS($B:B),1)

As the string returned is a single digit, Excel will have no problem using it in the addtion.

On Wed, 29 Dec 2010 01:09:54 +0000, jdb264c wrote:

>

>

>Thank you both for your help! Adding 7 gets it right for almost all years.

>

>For example, when entering december 2009 and calculating enough weeks ahead it correctly tags January 4th as the Monday in week 1. But entering January 2010 as start month, it tags January 3rd on Monday week 53, hence Jan 1st would be on Friday week 52?? A similar problem occured around 1999-2000.

>

>Maybe I should start considering rebuilding the entire thing, doing the calculations in a completely different manner?

If you are going to use your current method, you are going to need to differentiate between years with 52 weeks and years with 53 weeks.  That's why you have that problem.

Or you could just set up the first week, and reference back with the +7 method, calculating the weeknumber from one of the week dates (rather than using the weeknumber to derive the week data).

...

=DATE($B$1;1;5)-WEEKDAY(DATE($B$1;1;3))+($A$3-1)*7+INT(SEARCH(LEFT(B4;3);"MånTisOnsTorFreLörSön")/3)

This works perfectly, so dragging the code to the right, through the week, gives me the 9 dates correctly. (With the last being Saturday x2 and Sunday x2).

...

It you keep this formula for the first week, i.e on row 3, but replace it for all other weeeks like this.

In cell B15: =B3+7 and drag it to the right

In cell B27: =B15+7 and drag it to the right

and so on.

Hope this helps / Lars-Åke

On Fri, 24 Dec 2010 22:11:42 +0000, jdb264c wrote:

>

>

>Hi Ron, thanks for helping out again!

>

>While looking around in my hunt for formulas I saw somewhere that 21 had been added in Excel 2010, and it actually seems to work, it returns the ISO weeknumber!

>

>

Unfortunately, I only have 2007.

Without really understanding your layout and the constants you are using; if you start with:

=WEEKNUM(DATE(Year;MATCH(Month;{"Januari";"Februari";"Mars";"April";"Maj";"Juni";"Juli";"Augusti";"September";"Oktober";"November";"December"};0);1);21)

giving the first week of interest, for the "next week" just add 7 to the "day" parameter within your DATE function.

e.g:

=WEEKNUM(DATE(Year;MATCH(Month;{"Januari";"Februari";"Mars";"April";"Maj";"Juni";"Juli";"Augusti";"September";"Oktober";"November";"December"};0);8);21)