Exclude weekends when counting days only on day 29 or 30
 

I have a formula that returns a date 30 calendar days from a given date.
Example, cell A1 has 02/14/08. I want to add 30 days to this date and find
out what date that is but if that date is a Saturday or a Sunday, I want the
previous business day which is a Friday. I want to count all Saturdays and
Sundays during the 30 day cycle EXECPT if the 30th day is a Saturday or a
Sunday, I need to have a date returned that will back it up to the last
business day.

In other words, if today were February 15th and I had 30 calendar days to
complete my task, that would be March 16th, but since March 16th is a Sunday,
I need the formula to return the last business day (March 14th) on or before
day # 30.

Exclude weekends when counting days only on day 29 or 30
 

Maybe this:

=A1+30-CHOOSE(WEEKDAY(A1+30,2),0,0,0,0,0,1,2)


--
Biff
Microsoft Excel MVP


"Loren A - Huntley, Illinois" <Loren A - Huntley,
wrote in message
...
I have a formula that returns a date 30 calendar days from a given date.
Example, cell A1 has 02/14/08. I want to add 30 days to this date and
find
out what date that is but if that date is a Saturday or a Sunday, I want
the
previous business day which is a Friday. I want to count all Saturdays
and
Sundays during the 30 day cycle EXECPT if the 30th day is a Saturday or a
Sunday, I need to have a date returned that will back it up to the last
business day.

In other words, if today were February 15th and I had 30 calendar days to
complete my task, that would be March 16th, but since March 16th is a
Sunday,
I need the formula to return the last business day (March 14th) on or
before
day # 30.

Exclude weekends when counting days only on day 29 or 30
 

One way:
=A2+30-(WEEKDAY(A2+30,3)-4)*(WEEKDAY(A2+30,3)=5)
It can be simplified, but at least that shows what's being done.
--
David Biddulph

"Loren A - Huntley, Illinois" <Loren A - Huntley,
wrote in message
...
I have a formula that returns a date 30 calendar days from a given date.
Example, cell A1 has 02/14/08. I want to add 30 days to this date and
find
out what date that is but if that date is a Saturday or a Sunday, I want
the
previous business day which is a Friday. I want to count all Saturdays
and
Sundays during the 30 day cycle EXECPT if the 30th day is a Saturday or a
Sunday, I need to have a date returned that will back it up to the last
business day.

In other words, if today were February 15th and I had 30 calendar days to
complete my task, that would be March 16th, but since March 16th is a
Sunday,
I need the formula to return the last business day (March 14th) on or
before
day # 30.

Exclude weekends when counting days only on day 29 or 30
 

On Tue, 19 Feb 2008 10:51:01 -0800, Loren A - Huntley, Illinois <Loren A -
Huntley, wrote:

I have a formula that returns a date 30 calendar days from a given date.
Example, cell A1 has 02/14/08. I want to add 30 days to this date and find
out what date that is but if that date is a Saturday or a Sunday, I want the
previous business day which is a Friday. I want to count all Saturdays and
Sundays during the 30 day cycle EXECPT if the 30th day is a Saturday or a
Sunday, I need to have a date returned that will back it up to the last
business day.

In other words, if today were February 15th and I had 30 calendar days to
complete my task, that would be March 16th, but since March 16th is a Sunday,
I need the formula to return the last business day (March 14th) on or before
day # 30.


With the Analysis Tool Pak installed:

=WORKDAY(A1+31,-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.
Syntax




See HELP for this: Note that holidays is an optional argument, so if A1+30
should fall on a holiday, or if Friday happened to be a holiday, you would
still return the business day prior to A1+30
--ron