This works good for what you originally asked for

Sub Weekday()
Dim TDate, YDate As Date
Dim NDate As Integer
If Selection.Cells.Count > 1 Or IsEmpty(Selection) Then Exit Sub
TDate = Selection
NDate = WorksheetFunction.Weekday(TDate, 2) '("12/04/13")
Select Case NDate
    Case 7
    YDate = TDate - 2
    Case 2, 3, 4, 5, 6
    YDate = TDate - 1
    Case 1
    YDate = TDate - 3
End Select
Selection = Format(YDate, "MM/DD/YYYY")
End Sub
Make sure you have selected a cell before running macro.

the only way you can get the cell to contain todays date -1 when is to run another macro.
I would suggest placing one in this workbook module for workbook_open procedure

something like Sheet1.Range("A1"). value = today()

you cant use the worksheet function because the other macro will always overwrite it.