Formula assistance

[Ketan Mehta]

Hi

I would be grateful for assistance with a formula that I require.

In the attached spreadsheet, the activity sheet shows type of study, week number, Day, start and end times, I would like to enter a formula in the Date field (Yellow column) that looks at all the week number and Day in the Dates sheet and outputs the correct date in the yellow column.

For example, row 8 on the Activity sheet has week number 22 and the day is a Monday, the formula would look at this in the Dates sheet and the date for this is 18/02/2019, this is what should be returned in the date field (yellow column) by the formula. I want a formula to return all dates.

Thanks

 

[vletm]

Ketan Mehta
one possible ...
for cell F2
=INDEX(DATES!$A$1:$F$53,B2+1,MATCH(C2,DATES!A$1:F$1,0))
and copy down

 

[Peter Bartholomew]

An alternative approach that doesn't need to use the 'Dates' table.
= (base-1) + 7*([@[Week number]] - 1) + MATCH([@Day], DaysOfWeek,0)
where 'DaysOfWeek' is the named constant
={"Monday","Tuesday","Wednesday","Thursday","Friday"}

 

[p45cal]

Again, not needing a DATES sheet at all, no defined names and minimal alteration to the existing sheet, put date 24th Sept 2018 in cell G1 and this formula in cell F2:

=(B2-1)*7+$G$1+MATCH(C2,{"Monday","Tuesday","Wednesday","Thursday","Friday"},0)-1

and copy down.
It uses the same principle as Peter Bartholemew's solution.

 

[Ketan Mehta]

Thanks everyone for your time and suggestions, has proved very useful and achieved what I wanted.