You mean that if there are 3 dates, he wants the difference between the
second and the third?

BTW, this all seems odd, what is it for?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Mendz5" wrote in message
...
Bob,

Now the boss doesn't want the first day of the range to count. This was
the
first formula you gave me with the 3 columns:

=IF(COUNTIF(A1:C1,"<")=3,NETWORKDAYS(MIN(A1:C1),M AX(A1:C1)),
IF(COUNTIF(A1:C1,"<")<2,"N/A",
ABS(NETWORKDAYS(IF(A1<"",A1,B1),IF(OR(A1="",B1="" ),C1,B1)))))

I think a "+1" has to be added but I'm not sure where in the formula to
place it.

Thanks,

Mendz

"Bob Phillips" wrote:

Adds 0 if both dates are not present.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Mendz5" wrote in message
...
Bob,

Sorry to be a pest, but what does the zero do?

Mendz

"Bob Phillips" wrote:

The ABS is just so that I don't have to worry whether the first date
is
earlier or later than the second, saves testing for it.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Mendz5" wrote in message
...
Bob,

Thanks again! What did adding the ABS and the zero at the end of
each
NETWORKDAYS function do? I'm just trying to understand the logic,
so
that
I
can use it in the future.

Thanks,

Mendz

"Bob Phillips" wrote:

=IF(COUNT(J2:K2,M2:N2,Q2:R2)<2,"N/A",
IF(COUNTIF(J2:K2,"<")=2,ABS(NETWORKDAYS(J2,K2)),0 )+
IF(COUNTIF(M2:N2,"<")=2,ABS(NETWORKDAYS(M2,N2)),0 )+
IF(COUNTIF(Q2:R2,"<")=2,ABS(NETWORKDAYS(Q2,R2)),0 ))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing
direct)

"Mendz5" wrote in message
...
Bob,

I'm back again. After looking at the results of the most
recent
formula,
I
realized that I didn't explain properly what I needed, sorry.

Anyway, here it is:

There are 3 sets of 2 columns, each set has a beginning and
ending
date,
for
example:

J2 - 7/26/06 and K2 8/4/06 that is the networkdays that
should be
counted

M2 - 8/8/06 and N2 8/9/06 this duration should be added to the
above
results

Q2 - 8/14/06 and R2 8/16/06 this duration should be added to
the
above
results.

Of course, if any one of the sets only has 1 date, then that
set
should
not
be included.

Here is what I have coded:





=IF(COUNT(J2:K2,M2:N2,Q2:R2)<2,"N/A",IF(COUNTIF(J2:K2,"<")=2,NETWORKDAYS(MI



N(J2:K2),MAX(J2:K2))+IF(COUNTIF(M2:N2,"<")=2,NETW ORKDAYS(MIN(M2:N2),MAX(M2:

N2))+IF(COUNTIF(Q2:R2,"<")=2,NETWORKDAYS(MIN(Q2:R 2),MAX(Q2:R2))))))

If all cells are populated, then the formula works.

If the first set of cells is missing 1 date, I get a "FALSE"
in
the
target
cell

If the second or third set of cells is missing one date, the
formula
only
returns the duration of the first set of cells.


Thanks,

Mendz

"Bob Phillips" wrote:

I wonder what inconsistent formula actually means?

Just looked it up and apparently it means that the formula
in
adjacent
cells
seem to follow a pattern, and the formula in that cell does
not
match
the
formula.

So it seems reasonable to just ignore it.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing
direct)

"Mendz5" wrote in message
...
Bob,

In 2003 when excel thinks there is an error it puts a
little
green
triangle
in the upper left corner of the cell. When you click on
the
flag,
a
drop
down appears with several options. At the top of the drop
down it
states
inconsistent formula. I selected ignore error.

The formula does appear to work properly. I took the
earliest
and
the
lastest dates and plugged them into one of the formulas
that I
know
work
and
received the same results.

So thanks again! This discussion group has been a great
help.

Mendz

"Bob Phillips" wrote:

I don't know, I don't have Excel 2003 so I don't know
what a
green
flag
actually means. Do you get the correct answer?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if
mailing
direct)

"Mendz5" wrote in
message

...
Bob,

After entering the formula a green flag appeared in
the
cell
and
states
that
the the formula is inconsistent. Should I ignore it?

Mendz

"Bob Phillips" wrote:

Hi Mendz,

I have found a better solution

=IF(COUNT(J2:K2,M2:N2,Q2:R2)<2,"N/A",

ABS(NETWORKDAYS(MIN(J2:K2,M2:N2,Q2:R2),MAX(J2:K2,M 2:N2,Q2:R2))))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if
mailing
direct)

"Mendz5" wrote in
message

...
Bob,

I'm back with something even more complex:

6 columns, but they are not next to each other.

J2, K2, M2, N2, Q2, R2

If only 1 cell is populated, then I want to put
"N/A"
in
the
target
cell

If 2 or more cells are populated, then I want to
put
the
number of
days in
the target cell

I think I can create a formula if all cells are
populated,
but
I'm
having
trouble figuring out how to exclude 1 or more
cells
that
are
blank.

Thanks,

Mendz

If t

"Bob Phillips" wrote:

Ok.

First, I check if all 3 cells are completed

COUNTIF(A1:C1,"<")=3

If this is true, I determine the earliest
(MIN(A1:C1))
and
latest
(MAX(A1:C1)) dates and calculate the difference
using

NETWORKDAYS(MIN(A1:C1),MAX(A1:C1))

If they are not all completed I check if any two
are

COUNTIF(A1:C1,"<")<2

and if so error with "N/A"

If any two are completed, then either A or B
must be
one
of
them, so
I
get
the first date with

IF(A1<"",A1,B1)

then I determine the second date as either (A or
B)
or
C,
depending
upon
whether the first chosen is A or B (for instance
if
A is
completed,
then
the
second date must be B or C). The formula for
this is