weekenum convert into the date of monday of the week

[sony]

dear

i have weeknum in column A
e.g.
47
48

i want a formula in column B so that I can convert the weeknumber into the date of monda of the week (week logic is Monday to Sun) 21-Nov-2006 and 28-Nov-2006 is the result i need ot get

e.g.
col A ColB
47 21-Nov-2006
48 28-Nov-2006

What fomula i put in Coulmn B can achieve that?

thank you

 

[Oaktree]

Try:

D1 = 1/1/2006, D2 = 47 for 47th week
monday of 47th week =D1+7*(D2-IF(WEEKDAY(D1,2)<7,2,1))+7-WEEKDAY(D1,3)

Note: Nov 21, 2006 is a Tuesday. This formula returns Nov 20, 2006 for week number 47.

edited to make non-year specific

 

[nbrcrunch]

http://www.mrexcel.com/board2/viewtopic.php?t=159547

 

[sony]

Thank you !!!

what if more complicate, say, i need to convert the week of different years intot the date of monday of the week of the year?

colA colB
e.g. 200547 21-Nov-2005
200640 2-Nov-2005

etc


any formula can handle?

thank you again

 

[Oaktree]

Did you the edited formula I suggested?

 

[nbrcrunch]

I haven't fully tested this, but building on Oaktree's excellent submission...

If you want to expand this even further and have the option of specifying which day of the week you want:

=A26+7*(A27-IF(WEEKDAY(A26,2)<7,2,1))+(A28-2)-WEEKDAY(A26,3)+7

where:
A26 holds the 1/1 date for the year to be worked on
A27 holds the integer value of the weeknum being sought
A28 holds the integer value of the day-of-week being sought (1=Sunday)

P.S. Thank you, Oaktree. I saved this one in permanent library of reference formulas.

 

[barry houdini]

If you have a year and week number in the format 200543

then you could use one of these two methods to determine the Monday of that week

Book1
	A	B	C	D	E	F
1
2	200501	01/01/2005	01	27/12/2004	27/12/2004
3	200543	01/01/2005	43	17/10/2005	17/10/2005
4	199206	01/01/1992	06	03/02/1992	03/02/1992
5	200008	01/01/2000	08	14/02/2000	14/02/2000
6	200101	01/01/2001	01	01/01/2001	01/01/2001
7
weeknum

formula in B2 to give 1st day of the year in question

=DATE(LEFT(A2,4),1,1)

formula in C2 to extract week number from A2

=RIGHT(A2,LEN(A2)-4)

then in D2 to give date of the Monday of that week

=B2-6-WEEKDAY(B2,2)+7*C2

......or if you want to cut out the first two steps simply use one formula as in E2

=DATE(LEFT(A2,4),,26)-WEEKDAY(DATE(LEFT(A2,4),1,1),2)+(7*RIGHT(A2,LEN(A2)-4))