How does the following formula set the month based on week number?

Originally Posted by OldManExcellor

.....I think it chooses month based on in which month the wednesday of the week is. Is that correct?

It's difficult to say - it depends how currWeek is defined - are they ISO weeks or something else?

Also it doesn't make sense to me to have the second DATE function as this

=DATE(currWeek;1;3)

Are you sure that should be currWeek? As the first argument of DATE function it's more likely that should be currYear

I assume these must be ISO weeks

ISO weeks are defined such that week 1 starts on the first Monday on or after 29th December......so that would make the first Wednesday fall between 31st Dec and 6th Jan, which fits with what your formula does (if you change the second instance of currWeek to currYear, as suggested)

So this part

=DATE(currYear;1;currWeek*7)-WEEKDAY(DATE(currYear;1;3))

when currWeek = 1 will give you the first Wednesday on or after 31st December....and then as you increment currWeek throughout the year it will give you each subsequent Wednesday, so yes, you are right, as long as currWeek gives the ISO week number (which can be 53 in some years) you will get the month of the Wednesday of that week.

This can mean that week 1 will be in Dec (when week 1 starts on Monday 29th December, which will be the case for 2015).

The way ISO numbers are designed you can only ensure that week 1 will always be in Jan and also week 52/53 in Dec if you use the Thursday to determine the month.

Note also that if you have Swedish regional settings you could use TEXT function to shorten the formula, although I don't think you'll get the month starting with a capital letter, e.g. with this version

=TEXT(DATE(currYear;1;currWeek*7)-WEEKDAY(DATE(currYear;1;3));"MMM")

-2 will give you the start of the week (i.e. the Monday), so -1 will give you the Tuesday, not adding or subtracting anything (like your formula) gives the Wednesday so Thursday is +1

....also if you are just deriving currYear and currWeek from the current date then there's really no need to use those to find out the "month" of the current week, you can do that directly from today's date, e.g. this formula

=TEXT(TODAY()-WEEKDAY(TODAY();3)+3;"MMM")