Take week number and pull over the date of that Saturday

[lmcghee]

So I have a week number of the year : 46, 47, etc. I want a formula to look at that week and give me the date of the Saturday in that week. Ideas?

Example: week 46 would be Nov 16 as that Saturday. I want a formula to bring in 11/16/19.

 

[Fluff]

How about
=(DATE(2019,1,-2)-WEEKDAY(DATE(2019, 1, 3))+A2*7)+5

 

[Yongle]

with date in A2 try this
=A2+7-WEEKDAY(A2)

The posted range is not as per the worksheet - so image added

Book1
	A	B	C	D	E
1	Date	week	day	Saturday	formula
2	01/01/2019	1	01/01/2019	05/01/2019	=A2+7-WEEKDAY(A2)
3	02/01/2019	1	02/01/2019	05/01/2019
4	03/01/2019	1	03/01/2019	05/01/2019
5	04/01/2019	1	04/01/2019	05/01/2019
6	05/01/2019	1	05/01/2019	05/01/2019
7	06/01/2019	2	06/01/2019	12/01/2019
8	07/01/2019	2	07/01/2019	12/01/2019
9	08/01/2019	2	08/01/2019	12/01/2019
10	09/01/2019	2	09/01/2019	12/01/2019
11	10/01/2019	2	10/01/2019	12/01/2019
12	11/01/2019	2	11/01/2019	12/01/2019
13	12/01/2019	2	12/01/2019	12/01/2019
14	13/01/2019	3	13/01/2019	19/01/2019
15	14/01/2019	3	14/01/2019	19/01/2019
16	15/01/2019	3	15/01/2019	19/01/2019
17	16/01/2019	3	16/01/2019	19/01/2019
18	17/01/2019	3	17/01/2019	19/01/2019
19	18/01/2019	3	18/01/2019	19/01/2019
20	19/01/2019	3	19/01/2019	19/01/2019
21	20/01/2019	4	20/01/2019	26/01/2019
22	21/01/2019	4	21/01/2019	26/01/2019
23	22/01/2019	4	22/01/2019	26/01/2019
24	23/01/2019	4	23/01/2019	26/01/2019
25	24/01/2019	4	24/01/2019	26/01/2019
26	25/01/2019	4	25/01/2019	26/01/2019
27	26/01/2019	4	26/01/2019	26/01/2019
28	27/01/2019	5	27/01/2019	02/02/2019
Sheet17

Cell Formulas
Range	Formula
B2, B3:B28	B2	=WEEKNUM(A2)
C2, C3:C28	C2	=A2
D2, D3:D28	D2	=A2+7-WEEKDAY(A2)
E2	E2	=" "&FORMULATEXT(D2)

 

[Yongle]

ignore my suggestion - @Fluff reply made me realise that I did not read requirements correctly

 

[Yongle]

Same as @Fluff formula , but simpler

=DATE(2019,1,1)-WEEKDAY(DATE(2019,1,1))+A2*7

last Saturday of previous year
=DATE(2019,1,1)-WEEKDAY(DATE(2019,1,1))
plus number of day to add
+A2*7

 

[Fluff]

@imcghee
Whilst my formula follows the ISO week numbering system, where week 1 is the week containing the first Thursday of the year
The formula Yongle has supplied uses week 1 is the week containing 1st January, which is the Xl default

Not sure which you want, but for some years they will give different results.

 

[Yongle]

Whilst my formula follows the ISO week numbering system, where week 1 is the week containing the first Thursday of the year
The formula Yongle has supplied uses week 1 is the week containing 1st January, which is the Xl default

@Fluff - Thanks for clarifying that - I did not even consider that little wrinkle

 

[Fluff]

No did I, I'm just used to the ISO system & didn't even realise that Xl doesn't use it.
Interestingly for 2016 Xl shows there are 53 weeks

 

[Yongle]

@Fluff
Try this one ...
=WEEKNUM(DATE(2028,12,31))

 

[Tetra201]

Another approach:

=WORKDAY.INTL(DATE(2019,1,0),A2,"1111101")