It seems to me there's a function built into VBA that knows about working days. Let's see, here.... Yeah, here it is: WEEKDAY. You can get WEEKDAY to tell you what day of the week is the date you feed it; the return value is a number 1 through 7 where, by default, 1 is Sunday and 7 is Saturday.
On Sunday you want to you program to subtract 2 days from today's date, so that it can look at Friday; on Mon, subtract 3; on Tue through Sat, just 1. So here's one way to do it:
Code:
vd = Int(Now) 'today's date
vw = Weekday(vd) 'day of the week (1 through 7)
If vw > 2 then vw = 0
TargetDate = vd - vw - 2
I haven't tested this code, but if you work through it for Saturday (7), Sunday (1), Monday (2) and Tuesday (3) I think you'll see it works. There are other ways to do it, of course; this may be one of the simplest.
If your application needs to handle odd work days, you'd have to do it differently.