how to find out the months when we have week numbers particular year??

uttam.xlsxHi Guys,

please help me out in this

i need the months name when we have week numbers

Example

week1,2,3,4...... in a callender year it will come in jan month

like this

5,6,7,8 weeks in which month will come???

is there any formula to find ln excel???

when we have week numbers we need to get month names

Perhaps

Formula:

Please Login or Register  to view this content.

when does week 1 start?

we still don't know when week 1 starts.. there are several ways of choosing week 1 week 5 in excel 2007 for 2012 can be either

29/01/2012 to 04/02/2012
or
23/01/2012 to 29/01/2012
also there are other systems to consider
iso week 5 is
30/01/2012 to 05/02/20122 so what month would you say any or all of those week 5 fall in?

In A1 type a number. Let's say 10

In another cell-let's say c1, put the suggested formula.

Also see posts#3 & 5

Formula:

Please Login or Register  to view this content.

You haven't clarified how your week numbers are defined as Martin asked so I'll assume you are using week numbers as defined by WEEKNUM function in excel. Using that method week 1 always starts on 1st January and then week 2 starts on the following Sunday.

Ghozi's method doesn't work consistently

Originally Posted by Ghozi Alkatiri

=TEXT(MATCH(--RIGHT($B2,2),WEEKNUM(EDATE(1,12*(LEFT($A$1,4)-1900))-1+ROW(1:365)),0),"mmmm")

If you are going to copy that down the column then you need to change ROW(1:365) to ROW($1:$365) otherwise it will change each row as you copy down (and distort the results), although it's better to use ROW(INDIRECT("1:365")) in my view because that's more robust against row deletion/insertion.

....but even if you fix that the formula doesn't work - week 23 in 2014 starts on 1st June but the above formula gives you May for the start date. The reason for that is that the MATCH function gives you 152 for that week, which results in this

=TEXT(152,"mmmm")

Now 152 is day 152 in 1900........but excel treats 1900 as a leap year so day 152 is 31st May, whereas in 2014 - which isn't a leap year - day 152 is 1st June......so the formula gets the wrong result.

From that you can see that the formula will give you results 1 day early from March onwards in all non leap years and will therefore give you incorrect results in all cases where the week starts on 1st month in those months.

Better to use an adaptation of the formula Fotis1991 suggests, i.e. for start month of the week

=TEXT(DATE(LEFT(A$1,4),1,MAX(1,RIGHT(B2,2)*7-5-WEEKDAY(DATE(LEFT(A$1,4),1,1)))),"mmmm")

and for end month

=TEXT(DATE(LEFT(A$1,4),1,MIN(365,RIGHT(B2,2)*7+1-WEEKDAY(DATE(LEFT(A$1,4),1,1)))),"mmmm")

neither of which requires CTRL+SHIFT+ENTER

As per WEEKNUM no weeks cross over two years so week 1 always starts in January and weeks 53 and 54 (if it exists) end in December. I assume you won't list any weeks which don't exist in the year in question (again as per WEEKNUM definition)