Assign each week number to corresponding month of the year

[Alin Ababei]

Hi guys,

I have a series of consecutive dates in column A (Jan 1st 2005 - Dec
31st 2015) which i used to calculate their corresponding week number
(ISO method) in column B. I want to associate each of these week
numbers with their corresponding month (1-12 format).
The issue i'm having is that in some cases one week has some days in
one month and the rest in the next.
for example: w48 from 2010 (29 Nov - 5 Dec) should be assigned to
December as it the majority of days are in December.

Your help will be much appreciated

 

[Bob Phillips]

Can we see your formula to know what we are working with?

"Alin Ababei" wrote in message

Hi guys,

I have a series of consecutive dates in column A (Jan 1st 2005 - Dec
31st 2015) which i used to calculate their corresponding week number
(ISO method) in column B. I want to associate each of these week
numbers with their corresponding month (1-12 format).
The issue i'm having is that in some cases one week has some days in
one month and the rest in the next.
for example: w48 from 2010 (29 Nov - 5 Dec) should be assigned to
December as it the majority of days are in December.

Your help will be much appreciated

 

[alanglloyd]

Hi guys,

I have a series of consecutive dates in column A (Jan 1st 2005 - Dec
31st 2015) which i used to calculate their corresponding week number
(ISO method) in column B. I want to associate each of these week
numbers with their corresponding month (1-12 format).
The issue i'm having is that in some cases one week has some days in
one month and the rest in the next.
for example: w48 from 2010 (29 Nov - 5 Dec) should be assigned to
December as it the majority of days are in December.

If you think about it, what you want is the month of the Wednesday
(if only working days in your week) or the Thursday (if all days are
in your week) of the week. And you know the date-value of the Monday
in your week. So :

= Month(<date-value-for-Monday> + 2)

Replace the 2 with a 3 if you use all days

This gives you the month number, not its name.

Alternatively put =<date-value-for-Monday> + 2 (or 3) in your month
cell & custom format that cell as :

mmm

or

mmm yyyy

as you wish, to give you the month name, or name & year.

Alan Lloyd

 

[Alin Ababei]

i have already received a solution from another group and uses as
input only the serial date... assuming the series of dates start are
located in A2 and downwards:
=MONTH(DATE(YEAR($A2),MONTH($A2)+
(WEEKDAY($A2,2)+DAY(DATE(YEAR($A2),MONTH($A2)+1,0))-DAY($A2)<4),0+((7-
WEEKDAY($A2,2)+DAY($A2))>3)))

The ISO method for determining week numbers is well documented on Chip
Pearson's website: http://www.cpearson.com/excel/WeekNumbers.aspx

 

[Alin Ababei]

Anyway, thank you for your time

 

[alanglloyd]

=MONTH(DATE(YEAR($A2),MONTH($A2)+
(WEEKDAY($A2,2)+DAY(DATE(YEAR($A2),MONTH($A2)+1,0))-DAY($A2)<4),0+((7-
WEEKDAY($A2,2)+DAY($A2))>3)))

That sounds like a lot of typing <g>

Alan Lloyd