NETWORKDAYS calc with 3 columns of dates

[Guest]

I have 3 columns of dates (A1, B1, C1).
Several different scenarios:

1. If A1 is blank, but B1 & C1 are populated, I want to put the result in
D1.

2. If B1 is blank, but A1 & C1 are populated, I want to put the result in
D1.

3. If C1 is blank, but A1 & B1 are populated, I want to put the result in
D1.

4. If A1 & B1 are blank, I want to put "N/A" in D1.

5. If A1 & C1 are blank, I want to put "N/A" in D1.

6. If B1 & C1 are blank, I want to put "N/A" in D1.

Why are there 3 date fields you ask, the powers that be want it that way,
lucky me.

Thanks,

Mendz

 

[Bob Phillips]

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

--
HTH

Bob Phillips

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

 

[Mark]

I have 3 columns of dates (A1, B1, C1).
Several different scenarios:

1. If A1 is blank, but B1 & C1 are populated, I want to put the result in
D1.

2. If B1 is blank, but A1 & C1 are populated, I want to put the result in
D1.

3. If C1 is blank, but A1 & B1 are populated, I want to put the result in
D1.

4. If A1 & B1 are blank, I want to put "N/A" in D1.

5. If A1 & C1 are blank, I want to put "N/A" in D1.

6. If B1 & C1 are blank, I want to put "N/A" in D1.

Why are there 3 date fields you ask, the powers that be want it that way,
lucky me.

Thanks,

Mendz

1. If A1 is blank, but B1 & C1 are populated, I want to put the
result in

D1.

What do you mean by the result? I cant help you if I don't understand
what you want in the cell

 

[Guest]

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.

When A1, B1 & C1 are populated, only A1 & B1 are added.

When B1 & C1 are popluated the result is always "1", no matter what I put in
C1.

Thanks,

Mendz

 

[Guest]

Mark,

Sorry about that. I want the result to be the total number of workdays
between the given dates.

Thanks,

Mendz

 

[Bob Phillips]

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),MAX(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)

 

[Guest]

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]

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)

 

[Guest]

Bob,

Thank you very much for the explanation. When you break it up like that, it
makes it so much clearer.

Thanks again,

Mendz

 

[Guest]

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]

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,M2:N2,Q2:R2))))

--
HTH

Bob Phillips

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

 

[Guest]

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]

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)

 

[Guest]

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]

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)

 

[Guest]

Bob,

Thanks, you've been a great help!!

Mendz

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)

 

[Guest]

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(MIN(J2:K2),MAX(J2:K2))+IF(COUNTIF(M2:N2,"<>")=2,NETWORKDAYS(MIN(M2:N2),MAX(M2:N2))+IF(COUNTIF(Q2:R2,"<>")=2,NETWORKDAYS(MIN(Q2:R2),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]

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

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

 

[Guest]

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]

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)