NETWORKDAYS calc with 3 columns of dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
=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)
 
Mendz5 said:
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
Click to expand...

1. If A1 is blank, but B1 & C1 are populated, I want to put the
result in
D1.
Click to expand...
What do you mean by the result? I cant help you if I don't understand
what you want in the cell
 
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
 
Mark,

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

Thanks,

Mendz
 
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)
 
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
 
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)
 
Bob,

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

Thanks again,

Mendz
 
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
 
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)
 
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
 
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)
 
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
 
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)
 
Bob,

Thanks, you've been a great help!!

Mendz

Bob Phillips said:
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)
Click to expand...
 
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
 
=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 said:
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:
Click to expand...
=IF(COUNT(J2:K2,M2:N2,Q2:R2)<2,"N/A",IF(COUNTIF(J2:K2,"<>")=2,NETWORKDAYS(MI
 
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
 
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)
 
Back
Top