A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Convert Week number into Month



 
 
Thread Tools Display Modes
  #1  
Old May 28th, 2010, 01:00 AM posted to microsoft.public.excel.worksheet.functions
kt
external usenet poster
 
Posts: 94
Default Convert Week number into Month

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.
  #2  
Old May 28th, 2010, 06:05 AM posted to microsoft.public.excel.worksheet.functions
Lars-Åke Aspelin[_4_]
external usenet poster
 
Posts: 74
Default Convert Week number into Month

On Thu, 27 May 2010 17:00:01 -0700, KT
wrote:

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.


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/ex...093371033.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
  #3  
Old May 28th, 2010, 12:02 PM posted to microsoft.public.excel.worksheet.functions
Steve Dunn
external usenet poster
 
Posts: 192
Default Convert Week number into Month

=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
...
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.


  #4  
Old May 28th, 2010, 06:19 PM posted to microsoft.public.excel.worksheet.functions
Lars-Åke Aspelin[_4_]
external usenet poster
 
Posts: 74
Default Convert Week number into Month

Remove the call to MONTH() and the formula will work better.
=TEXT(DATE(A1,1,MATCH(B1,INDEX(WEEKNUM(DATE(A1,1,R OW(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
...
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.


  #5  
Old May 28th, 2010, 06:23 PM posted to microsoft.public.excel.worksheet.functions
Lars-Åke Aspelin[_4_]
external usenet poster
 
Posts: 74
Default Convert Week number into Month

On Fri, 28 May 2010 07:05:23 +0200, Lars-Åke Aspelin
wrote:

On Thu, 27 May 2010 17:00:01 -0700, KT
wrote:

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.


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/ex...093371033.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



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

Lars-Åke
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:27 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.