View Single Post
  #20   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips Bob Phillips is offline
external usenet poster
 
Posts: 10,593
Default NETWORKDAYS calc with 3 columns of dates

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

IF(OR(A1="",B1=""),C1,B1)

I pass the two selected dates to NETWORKSDAYS and ABS it

in
case
I
don't
pass them in date order.

That's it.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if

mailing
direct)

"Mendz5" wrote in

message

...
Bob,

Thank you so much, the formula works perfectly. I

know
the
basic
functions
of excel, could you explain the formula you created.

Thanks,

Mendz

"Bob Phillips" wrote:

Mendz,

No it was my error on the second part. This corrects

the
always
1
problem







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

I though A1 and B1 and C1 was not a valid condition,

so
didn't
cater
for
it.
What do you want to happen if they are all present

as
NETWORKDAYS
only
works
on 2 dates. If you want earliest to latets then

perhaps,


=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)))))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if

mailing
direct)

"Mendz5" wrote in

message

...
Bob,

The formula almost works, unless I fat fingered

something,
which
is
entirely
possible (I'll double check again).

When A1 & B1 are populated the calculation works.

When A1 & C1 are populated the calculation works.