Bob,

No,

If A1 = 7/06/06, B1 = 7/11/06 and C1 = 7/13/06

He wants to begin counting with A1 plus 1 day

If A1 is blank, B1 = 7/11/06 and C1 = 7/13/06

he wants to use B1 + 1 day

If A1 = 7/06/06, B1 is blank and C1 = 7/13/06

He wants to begin counting with A1 plus 1 day

We have a process where project documents are shuffled from one group to
another and he wants to know the duration of time that the documents spend in
each group. Some dates we have, some dates are just not available, that's
why some of the cells can be blank.

Essentially, he has to justify why some projects are being delayed.

Thanks,

Mendz

"Bob Phillips" wrote:

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