date calcualtion

[Guest]

I am trying to return a date that is seven days prior to the date I choose,
but I want to omit Saturday and Sunday. Therefore if the date of the event
is Sunday March 18, 2007 and I want to receive payment seven days prior I
would like to have the formula automatically return a date seven days prior,
but since that day would be Sunday I need to return Friday as the date I need.

 

[Roger Govier]

Hi

Provided you have the Analysis Toolpak loaded, Tools>Addins>check
Analysis Toolpak
then
=WORKDAY(A1,-6)
should do what you want.

 

[Guest]

=IF(WEEKDAY(A1-7)=1,A1-9,IF(WEEKDAY(A1-7)=7,A1-8,A1-7))

 

[Ron Rosenfeld]

I am trying to return a date that is seven days prior to the date I choose,
but I want to omit Saturday and Sunday. Therefore if the date of the event
is Sunday March 18, 2007 and I want to receive payment seven days prior I
would like to have the formula automatically return a date seven days prior,
but since that day would be Sunday I need to return Friday as the date I need.

=WORKDAY(A1-6,-1)

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

How?

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

 

[Ron Rosenfeld]

Hi

Provided you have the Analysis Toolpak loaded, Tools>Addins>check
Analysis Toolpak
then
=WORKDAY(A1,-6)
should do what you want.

Hmmm

Monday, March 19, 2007 --> Friday March 9, 2007
--ron

 

[Roger Govier]

Hi Ron

Of course, you are correct with your solution of
=WORKDAY(A1-6,-1)
but mine would get the money in sooner<vbg>
Thanks for the correction.