how to calculate the date from the year, week-of-year and day-of-week in EXCEL?-CodePudding

I have the Year, Week of Year and Day of the Week and I would like to estimate the Date as dd.mm.yyyy, which is highlighted in yellow as it shows in the EXCEL picture

I tried many formulas, but I am sure there might be an easy one.

CodePudding user response：

I found the solution:

Year = 2022 (A2) ; Week Year = 35 (B2); Week Day = 4 or Thursday (C2)

=DATE (A2,1,3)-WEEKDAY(DATE(A2,1,3)) 7 * B2 C5 - 6

CodePudding user response：

I found this solution, but you need to do further testing if it really works.

I calculate month from week: = MONTH(DATE(YEAR(A2);1;1) B2*7-1)
I calculate week day number from week day name: =MATCH(D2;{"Monday";"Tuesday";"Wednesday";"Thursday";"Friday";"Saturday";"Sunday"};0)

And then make date using: =DATE(A2;C2;E2)