Coding to show first _weekday_ prior to a date, when date calculation happens to fall on weekend?

[StargateFan]

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!

 

[Bob Phillips]

Pete,

use

=IF(B3<>"",B3-21-(WEEKDAY(B3-21)=1)*2-(WEEKDAY(B3-21)=7)*1,"")

and

=IF(B3<>"",B3-7-(WEEKDAY(B3-7)=1)*2-(WEEKDAY(B3-7)=7)*1,"")

 

[Bob Phillips]

another way

=IF(B3<>"",B3-21-CHOOSE(WEEKDAY(B3-21),2,0,0,0,0,0,1),"")

 

[Ron Rosenfeld]

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


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

=IF(B3<>"",workday(B3-6,-1),"")

If the WORKDAY function is not available, and returns the #NAME? error, install
and load the Analysis ToolPak add-in.

On the Tools menu, click Add-Ins.
In the Add-Ins available list, select the Analysis ToolPak box, and then click
OK.
If necessary, follow the instructions in the setup program.


--ron

 

[StargateFan]

Pete,

use

=IF(B3<>"",B3-21-(WEEKDAY(B3-21)=1)*2-(WEEKDAY(B3-21)=7)*1,"")

and

=IF(B3<>"",B3-7-(WEEKDAY(B3-7)=1)*2-(WEEKDAY(B3-7)=7)*1,"")

It sure did. It works here on my home version like a charm.

I found something odd, though. At work, I have to key in dd/mm/yyyy
for the calculation to work (day first). When I brought the
spreadsheet home it wouldn't work until I keyed in mm/dd/yyyy instead
(month first). I'm wondering why the difference? Is this a regional
setting thing, or an Excel setting thing somewhere, anyone know?

Once I know how to do that, I'll be able to watch out for this in
future. I've never run into this type of thing before. All my
spreadsheets have worked wherever I have gone. Since I don't modify
anything to do with this type of thing during my install of Excel, I'm
thinking this might be controlled somewhere else.

My last day is Friday, so I'll be able to leave _all_ the pending
events for the next 6 months that I've already received printed up and
in their respective BF (bring forward) folder so that the new person
will know exactly when to do all the email notifications for all of
these events. There were so many and it otherwise would have taken me
a long time to do manually.

Thanks so much, everyone! You're all great to give so much great
help. This will be a phenomenal sheet once I fix the one at work!
D

 

[StargateFan]

=IF(B3<>"",workday(B3-6,-1),"")

If the WORKDAY function is not available, and returns the #NAME? error, install

and load the Analysis ToolPak add-in.

Thanks, I'll look for it. I wouldn't be able to do that at work, as
you can imagine, as I can't install anything but it'll be a good
add-in to have here at home.

On the Tools menu, click Add-Ins.
In the Add-Ins available list, select the Analysis ToolPak box, and then click
OK.
If necessary, follow the instructions in the setup program.


--ron

Thank you!