# weekenum convert into the date of monday of the week

#### sony

##### Board Regular
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

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying
Try:

Code:
``````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

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

Did you the edited formula I suggested?

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.

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

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))

Replies
2
Views
274
Replies
5
Views
68
Replies
3
Views
117
Replies
4
Views
213
Replies
9
Views
646

1,203,469
Messages
6,055,601
Members
444,802
Latest member
lolica12

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back