Get Date from Year, Week Number, and Weekday

[KingLudovic]

Hello all,
I'm having an attempt at having a Date (1st, 2nd, etc.) output when the Year, Week Number, and Weekday are all inputted as a method to futureproof a staff rota I've created, as all that would have to change is the what's inputted into the year. I've tried to mess around with functions such as =WEEKDAY and =DATE, but I've hit a road block as I'm not exactly sure how I'd go about using them, and if they're the right functions for the job.

Here is an example of the "logic" I'm trying to make this follow:
CalculationExample.JPG

And here is my Template for the staff rota:
FinalTemplate.JPG

Please let me know if you need any further information, and thank you in advance!

[6StringJazzer]

You need to redesign your sheet. If you attach your actual file instead of just a picture I can help. See yellow banner at the top of the page.

I am going to suggest you reverse your thinking. B3 should have a date which is the first Monday of the year (formula can be seen below). Then all other dates just add 1 to the previous date. You can still display the days of the week with format Dddd. For showing the date of the day, in B2 use =B3, and so forth. It is not worth your trouble to show ordinal dates. You have to deal with the following endings for seven different cases: st, nd, rd, th. You can see in H11 below how this is done. But don't bother. Just show the number of the date using format d.

What you want is a very complicated formula. It would be very long. Here I show how to build it in steps. To make this one single formula would be very long and complicated.

Values as displayed

	G	H
1	Parameters:
2	Desired year	2018
3	Week of year	2
4	Desired day of the week	Thursday
5
6	Solution:
7	Date of the first of the desired year	Monday, January 1, 2018
8	First Monday	Monday, January 1, 2018
9	First Monday of week number	Monday, January 8, 2018
10	Date of day of week	Thursday, January 11, 2018
11	Day	11th

Underlying formulas

	G	H
1	Parameters:
2	Desired year	2018
3	Week of year	2
4	Desired day of the week	Thursday
5
6	Solution:
7	Date of the first of the desired year	=DATE(H2,1,1)
8	First Monday	=DATE(H2,1,1)+7-WEEKDAY(DATE(H2,1,1),12)
9	First Monday of week number	=H8+7*(H3-1)
10	Date of day of week	=H9+MATCH(H4,{"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"},0)-1
11	Day	=DAY(H10)&IF(OR(DAY(H10)={1,2,3,21,22,23,31}),CHOOSE(1*RIGHT(DAY(H10),1),"st","nd ","rd "),"th")

[KingLudovic]

That's exactly perfect for what I'm trying to do! Thank you so much for your help again, you've been a crucial part in making this.

[6StringJazzer]

Glad to help, thanks for the rep!