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

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

"Alin Ababei" wrote in message
news:4f063e4a-5e2d-4c12-a80e-(E-Mail Removed)...

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

On Sep 29, 6:37*am, Alin Ababei <alin.aba...@gmail.com> wrote:
> 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

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

Anyway, thank you for your time

On Sep 29, 10:43*am, Alin Ababei <alin.aba...@gmail.com> wrote:
<snip>
> =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