Subtract 3 days from Date with a Twist Q

[Sean]

How could I expess a formula that will show -3 days from a certain
date, but if the answer is a weekend date then it must return the
previous friday

Example: Cell A2= 15/11/07, I must return -3 days from this is A1, but
this = 11/11/07 which is a weekend date, thus I require an answer of
09/11/07 i.e. a Friday

Thanks

 

[Sean]

Slight update on above, the 3 days I require to deduct cannot be
weekend dates

Eg. 12/11/07 is a Monday -3 days from this is 09/11/07 a Friday, but
the answer I require is 07/11/07 a Wednesday. Thus the -3 days
subtracted must be week days

Thanks

 

[Niek Otten]

=WORKDAY(A2,-4)

If you get a #NAME error:
Tools>Add-ins, check Analysis Toolpak

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Slight update on above, the 3 days I require to deduct cannot be
| weekend dates
|
| Eg. 12/11/07 is a Monday -3 days from this is 09/11/07 a Friday, but
| the answer I require is 07/11/07 a Wednesday. Thus the -3 days
| subtracted must be week days
|
| Thanks
|
|
|

 

[bj]

Checkout the workday() funtion in Help.
it will do what you want also adding in holidays if you want.
if you get the #NAME? error, install and load the Analysis ToolPak add-in.

 

[Bob Phillips]

=WORKDAY(A2,-3)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Ron Rosenfeld]

Slight update on above, the 3 days I require to deduct cannot be
weekend dates

Eg. 12/11/07 is a Monday -3 days from this is 09/11/07 a Friday, but
the answer I require is 07/11/07 a Wednesday. Thus the -3 days
subtracted must be week days

Thanks

=workday(a1,-3)

If this 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

 

[Sean]

Thanks all, smart function