Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Convert Week number into Month

8,661 views
Skip to first unread message

KT

unread,
May 27, 2010, 8:00:01 PM5/27/10
to
I have a worksheet with values for year and week number. I need to convert
these to the descriptive month name. Some weeks cross month ends, but this
is okay, I just need a single value for each record. It could be the month
of the first day of each week number. Using Excel 2003.

Thanks for your ideas.

Lars-�ke Aspelin

unread,
May 28, 2010, 1:05:23 AM5/28/10
to
On Thu, 27 May 2010 17:00:01 -0700, KT <K...@discussions.microsoft.com>
wrote:

If your year is in cell A1 and you week number is in cell B2, try the
following formula:

=MONTH(DATE(A1,1,MATCH(TRUE,WEEKNUM(DATE(A1,1,ROW(A1:A366)),2)=B1,0)))

Note: This is an array formula that should be confirmed by
CTRL+SHIFT+ENTER rather than just ENTER.

Note 2: WEEKNUM does not support European standard for week number,
see
http://office.microsoft.com/en-us/excel/HP052093371033.aspx

If you use the European standard for week numbers, you have to put
some more logic into the formula to handle the case, like this year,
where the first few days of the year is not week number 1.
In Europe January 1-3 of 2010 is week number 53.

Hope this helps / Lars-�ke

Steve Dunn

unread,
May 28, 2010, 7:02:13 AM5/28/10
to
=TEXT(MONTH(DATE(A1,1,MATCH(B1,INDEX(WEEKNUM(DATE(A1,1,ROW(A1:A366)),2),),0))),"MMMM")

Which does *not* need to be array entered.

HTH
Steve D.


"KT" <K...@discussions.microsoft.com> wrote in message
news:6D51421C-548B-4A5D...@microsoft.com...

Lars-�ke Aspelin

unread,
May 28, 2010, 1:19:29 PM5/28/10
to
Remove the call to MONTH() and the formula will work better.
=TEXT(DATE(A1,1,MATCH(B1,INDEX(WEEKNUM(DATE(A1,1,ROW(A1:A366)),2),),0)),"MMMM")

Lars-�ke

On Fri, 28 May 2010 12:02:13 +0100, "Steve Dunn" <st...@sky.com>
wrote:

Lars-�ke Aspelin

unread,
May 28, 2010, 1:23:47 PM5/28/10
to


I made a typo there. The week number is in cell B1 (not B2) of course.

Lars-�ke

alovell

unread,
Jun 2, 2014, 2:18:30 AM6/2/14
to
This needs "ROW(A$1:A$366)" or you will get things like 6 weeks in January and the formula gets exhausted while still in November after 53 weeks.

On Friday, May 28, 2010 7:19:29 PM UTC+2, Lars-Åke Aspelin wrote:
> Remove the call to MONTH() and the formula will work better.
> =TEXT(DATE(A1,1,MATCH(B1,INDEX(WEEKNUM(DATE(A1,1,ROW(A1:A366)),2),),0)),"MMMM")
>
> Lars-�ke
>
> On Fri, 28 May 2010 12:02:13 +0100, "Steve Dunn"
> wrote:
>
> >=TEXT(MONTH(DATE(A1,1,MATCH(B1,INDEX(WEEKNUM(DATE(A1,1,ROW(A1:A366)),2),),0))),"MMMM")
> >
> >Which does *not* need to be array entered.
> >
> >HTH
> >Steve D.
> >
> >
> >"KT" wrote in message

glenn....@manpower.be

unread,
Jun 30, 2017, 8:45:58 AM6/30/17
to
What if your year number is in the same cell as the week number ?
0 new messages