Multiple substitute question.

Hello, I am quite new to Excel but I have come across a complicated problem (at least for me it is complicated).

I am trying to create a worsheet that automates codes for television file IDs. I have been successful in automating these with the exception of one cell. The cell that I need to formulate should be able to substitute a day (Monday, Tuesday, Wednesday...) that eminates from a cell in a different row with a number (1 to 7). Example: IF cell E1 has "Thursday", then L1 is "4". I am able to already do this with a SUBSTITUTE formula, However, the originating DAY cell is derived from another cell that contains a date.

My problem occurs since the DAY cell is random. I have tried in vein to create the argument =SUBSTITUTE(E1,"Monday","1",1) plus =SUBSTITUTE(E1,"Tuesday","2",1) plus =SUBSTITUTE(E1,"Wednesday","3",1) all the way thru plus =SUBSTITUTE(E1,"Sunday","7",1)

Is this at all possible or is my syntax just wrong (obviously it is wrong)

I would really appreciate any help.

Thanks in advance.

Perhaps, if I've understood, use a table / lookup based solution

L1: =VLOOKUP(E1,{"Monday",1;"Tuesday",2;"Wednesday",3;"Thursday",4;"Friday",5;"Saturday",6;"Sunday",7},2,0)

(in the above the table is stored via an inline array rather than in a range)

EDIT: missed the "...the originating DAY cell is derived from another cell that contains a date" - above therefore unwarranted.