Subtract 3 days from Date with a Twist Q

S

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
 
S

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
 
N

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
|
|
|
 
B

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.
 
B

Bob Phillips

=WORKDAY(A2,-3)

--
HTH

Bob

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top