I would make this a comment, but then the information may be lost. You're looking for dependent validation. this page has some good information. And I quote:

Open a new Workbook and on Sheet1 enter these heading into cells A1:D1 Expensive Cars, Cities, Names, Countries

Now place some relevant entries below these headings, down to say row 5. Now select A1:D1, click in the Name Box (left of the formula bar) type the name: List1 and push Enter. Now select A1:D5 (or the last row of the longest list) and go to Insert>Name>Create. Ensure only "Top Row" is checked and click Ok. If you now go back to the Name Box you will see 5 Named ranges. Note how Excel has used the Underscore for "Expensive_Cars". This is because Named ranges cannot have spaces.

Now click onto Sheet2 and select cell A1. Go to Data>Validation, choose the "List" option then type: =List1 in the Source box. Ensure "In-cell dropdown" is checked and click Ok. Now select cell A2 and again go to Data>Validation, choose the "List" option then type: =INDIRECT(SUBSTITUTE($A$1," ","_")) in the Source box. Ensure "In-cell dropdown" is checked and click Ok.

Now select a list from cell A1 and you will get the appropriate list in cell A2.

Notes

• Normally, a Validation list cannot refer to a list from another Worksheet. This can be overcome by naming the list and using its Name over its address.
• The INDIRECT Function return the reference returned by a text string. This means, rather than the Validation List in A2 seeing the content of A1 as a text string, it sees it as a range name .
• The SUBSTITUTE Function is used to replace any spaces in the text of A1 with a Underscore. This is vital when we have items in List1 like: Expensive Cars.

It seems that the date you want is two days earlier than the date in cell A1, except that if the result is on a weekend, it should be the Friday before the weekend.

You can use the WEEKDAY function to return a number representing the day of the week a date is (1=Sunday, 7=Saturday).

A simple way to get the date you want is

=IFS(WEEKDAY(A1-2)=1,A1-4,WEEKDAY(A1-2)=7,A1-3,TRUE,A1-2)

If your version of Excel doesn't have the IFS function, you can use nested IF functions:

=IF(WEEKDAY(A1-2)=1,A1-4,IF(WEEKDAY(A1-2)=7,A1-3,A1-2))