Convert Week number into Month
in [Functions]
|
From: KT on 27 May 2010 20:00 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.
From: Lars-�ke Aspelin on 28 May 2010 01:05 On Thu, 27 May 2010 17:00:01 -0700, KT <KT(a)discussions.microsoft.com> 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/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
From: Steve Dunn on 28 May 2010 07:02 =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" <KT(a)discussions.microsoft.com> wrote in message news:6D51421C-548B-4A5D-9B80-2234506154B0(a)microsoft.com... >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.
From: Lars-�ke Aspelin on 28 May 2010 13:19 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" <stunn(a)sky.com> 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" <KT(a)discussions.microsoft.com> wrote in message >news:6D51421C-548B-4A5D-9B80-2234506154B0(a)microsoft.com... >>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.
From: Lars-�ke Aspelin on 28 May 2010 13:23 On Fri, 28 May 2010 07:05:23 +0200, Lars-�ke Aspelin <larske(a)REMOOVEtelia.com> wrote: >On Thu, 27 May 2010 17:00:01 -0700, KT <KT(a)discussions.microsoft.com> >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/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 I made a typo there. The week number is in cell B1 (not B2) of course. Lars-�ke
|
Pages: 1 Prev: Convert Week number to Month Next: averageif with 3 crieteria |