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