I created a spreadsheet "date calculator" that has helped me
enormously in work. I'm with a legal firm's marketing dept and we
receive countless notices of events taking place at a local large
performance centre. My job is to send out an email each time to all
the lawyers so that they know what they can take clients to and then
alert my boss 3 weeks prior and then 1 week prior to get her feedback
on what action to take for each event based on the tickets requested
(or tickets not requested, as the case may be). The problem with this
is when the date returned falls on the weekend. I need for the
spreadsheet to return a weekday in all cases. So if the alert happens
to fall on a weekday, fine. But if it falls on a weekend, the
spreadsheet would need to offset that date to show the Friday just
previous to that weekend.

For example, there's an event on Sat Jan 29 2005 - the spreadsheet
returns Sat Jan 8th as 3 weeks before and Sat Jan 22 as 1 week before
as when I must update my boss. Any way to make the calculation return
the Friday before in this type of case??

The calculation is currently an easy one, I have the cell format to
show as a custom date of: ddd., mmm.dd.yyyy

And the 3 week code is:
=IF(B3<"",SUM(B3-21),"")

And the 1 week code is:
=IF(B3<"",SUM(B3-7),"")

Thanks much for any help!