NETWORKDAYS calc with 3 columns of dates

[Mendz5]

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)

"Mendz5" <Mendz5@discussions.microsoft.com> wrote in message
news:36FD65A9-2CEF-49D5-9B77-052054482DFE@microsoft.com...
> 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

[Mark]

Mendz5 wrote:
> 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

[Mendz5]

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

"Bob Phillips" wrote:

> =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" <Mendz5@discussions.microsoft.com> wrote in message
> news:36FD65A9-2CEF-49D5-9B77-052054482DFE@microsoft.com...
> > 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
>
>
>

[Mendz5]

Mark,

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

Thanks,

Mendz

"Mark" wrote:

>
> Mendz5 wrote:
> > 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
>
>

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

"Mendz5" <Mendz5@discussions.microsoft.com> wrote in message
news:0C2AE5BB-9456-44B5-A0EB-D516B591269F@microsoft.com...
> 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
>
> "Bob Phillips" wrote:
>
> >
=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" <Mendz5@discussions.microsoft.com> wrote in message
> > news:36FD65A9-2CEF-49D5-9B77-052054482DFE@microsoft.com...
> > > 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
> >
> >
> >

[Mendz5]

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),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)
>
> "Mendz5" <Mendz5@discussions.microsoft.com> wrote in message
> news:0C2AE5BB-9456-44B5-A0EB-D516B591269F@microsoft.com...
> > 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
> >
> > "Bob Phillips" wrote:
> >
> > >
> =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" <Mendz5@discussions.microsoft.com> wrote in message
> > > news:36FD65A9-2CEF-49D5-9B77-052054482DFE@microsoft.com...
> > > > 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]

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" <Mendz5@discussions.microsoft.com> wrote in message
news:F9441029-6722-494F-A541-EA5787C952A3@microsoft.com...
> 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),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)
> >
> > "Mendz5" <Mendz5@discussions.microsoft.com> wrote in message
> > news:0C2AE5BB-9456-44B5-A0EB-D516B591269F@microsoft.com...
> > > 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
> > >
> > > "Bob Phillips" wrote:
> > >
> > > >
> >
=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" <Mendz5@discussions.microsoft.com> wrote in message
> > > > news:36FD65A9-2CEF-49D5-9B77-052054482DFE@microsoft.com...
> > > > > 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
> > > >
> > > >
> > > >
> >
> >
> >

[Mendz5]

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 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" <Mendz5@discussions.microsoft.com> wrote in message
> news:F9441029-6722-494F-A541-EA5787C952A3@microsoft.com...
> > 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),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)
> > >
> > > "Mendz5" <Mendz5@discussions.microsoft.com> wrote in message
> > > news:0C2AE5BB-9456-44B5-A0EB-D516B591269F@microsoft.com...
> > > > 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
> > > >
> > > > "Bob Phillips" wrote:
> > > >
> > > > >
> > >
> =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" <Mendz5@discussions.microsoft.com> wrote in message
> > > > > news:36FD65A9-2CEF-49D5-9B77-052054482DFE@microsoft.com...
> > > > > > 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
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

[Mendz5]

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" <Mendz5@discussions.microsoft.com> wrote in message
> news:F9441029-6722-494F-A541-EA5787C952A3@microsoft.com...
> > 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),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)
> > >
> > > "Mendz5" <Mendz5@discussions.microsoft.com> wrote in message
> > > news:0C2AE5BB-9456-44B5-A0EB-D516B591269F@microsoft.com...
> > > > 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
> > > >
> > > > "Bob Phillips" wrote:
> > > >
> > > > >
> > >
> =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" <Mendz5@discussions.microsoft.com> wrote in message
> > > > > news:36FD65A9-2CEF-49D5-9B77-052054482DFE@microsoft.com...
> > > > > > 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]

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)

"Mendz5" <Mendz5@discussions.microsoft.com> wrote in message
news:DF60CCAE-A466-4D93-B0C1-5E74D2C921AA@microsoft.com...
> 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" <Mendz5@discussions.microsoft.com> wrote in message
> > news:F9441029-6722-494F-A541-EA5787C952A3@microsoft.com...
> > > 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),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)
> > > >
> > > > "Mendz5" <Mendz5@discussions.microsoft.com> wrote in message
> > > > news:0C2AE5BB-9456-44B5-A0EB-D516B591269F@microsoft.com...
> > > > > 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
> > > > >
> > > > > "Bob Phillips" wrote:
> > > > >
> > > > > >
> > > >
> >
=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" <Mendz5@discussions.microsoft.com> wrote in message
> > > > > > news:36FD65A9-2CEF-49D5-9B77-052054482DFE@microsoft.com...
> > > > > > > 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
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

[Mendz5]

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,M2:N2,Q2:R2))))
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Mendz5" <Mendz5@discussions.microsoft.com> wrote in message
> news:DF60CCAE-A466-4D93-B0C1-5E74D2C921AA@microsoft.com...
> > 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" <Mendz5@discussions.microsoft.com> wrote in message
> > > news:F9441029-6722-494F-A541-EA5787C952A3@microsoft.com...
> > > > 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),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)
> > > > >
> > > > > "Mendz5" <Mendz5@discussions.microsoft.com> wrote in message
> > > > > news:0C2AE5BB-9456-44B5-A0EB-D516B591269F@microsoft.com...
> > > > > > 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
> > > > > >
> > > > > > "Bob Phillips" wrote:
> > > > > >
> > > > > > >
> > > > >
> > >
> =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" <Mendz5@discussions.microsoft.com> wrote in message
> > > > > > > news:36FD65A9-2CEF-49D5-9B77-052054482DFE@microsoft.com...
> > > > > > > > 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]

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" <Mendz5@discussions.microsoft.com> wrote in message
news:CFC0D88B-B3A1-4998-AFCC-E964223126D1@microsoft.com...
> 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,M2:N2,Q2:R2))))
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > "Mendz5" <Mendz5@discussions.microsoft.com> wrote in message
> > news:DF60CCAE-A466-4D93-B0C1-5E74D2C921AA@microsoft.com...
> > > 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" <Mendz5@discussions.microsoft.com> wrote in message
> > > > news:F9441029-6722-494F-A541-EA5787C952A3@microsoft.com...
> > > > > 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),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)
> > > > > >
> > > > > > "Mendz5" <Mendz5@discussions.microsoft.com> wrote in message
> > > > > > news:0C2AE5BB-9456-44B5-A0EB-D516B591269F@microsoft.com...
> > > > > > > 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
> > > > > > >
> > > > > > > "Bob Phillips" wrote:
> > > > > > >
> > > > > > > >
> > > > > >
> > > >
> >
=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" <Mendz5@discussions.microsoft.com> wrote in message
> > > > > > > > news:36FD65A9-2CEF-49D5-9B77-052054482DFE@microsoft.com...
> > > > > > > > > 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
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

[Mendz5]

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" <Mendz5@discussions.microsoft.com> wrote in message
> news:CFC0D88B-B3A1-4998-AFCC-E964223126D1@microsoft.com...
> > 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,M2:N2,Q2:R2))))
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (replace somewhere in email address with gmail if mailing direct)
> > >
> > > "Mendz5" <Mendz5@discussions.microsoft.com> wrote in message
> > > news:DF60CCAE-A466-4D93-B0C1-5E74D2C921AA@microsoft.com...
> > > > 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" <Mendz5@discussions.microsoft.com> wrote in message
> > > > > news:F9441029-6722-494F-A541-EA5787C952A3@microsoft.com...
> > > > > > 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),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)
> > > > > > >
> > > > > > > "Mendz5" <Mendz5@discussions.microsoft.com> wrote in message
> > > > > > > news:0C2AE5BB-9456-44B5-A0EB-D516B591269F@microsoft.com...
> > > > > > > > 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
> > > > > > > >
> > > > > > > > "Bob Phillips" wrote:
> > > > > > > >
> > > > > > > > >
> > > > > > >
> > > > >
> > >
> =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" <Mendz5@discussions.microsoft.com> wrote in message
> > > > > > > > > news:36FD65A9-2CEF-49D5-9B77-052054482DFE@microsoft.com...
> > > > > > > > > > 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]

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" <Mendz5@discussions.microsoft.com> wrote in message
news:97442693-F65B-49CE-84DA-E997222CB7CC@microsoft.com...
> 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" <Mendz5@discussions.microsoft.com> wrote in message
> > news:CFC0D88B-B3A1-4998-AFCC-E964223126D1@microsoft.com...
> > > 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,M2:N2,Q2:R2))))
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > (replace somewhere in email address with gmail if mailing direct)
> > > >
> > > > "Mendz5" <Mendz5@discussions.microsoft.com> wrote in message
> > > > news:DF60CCAE-A466-4D93-B0C1-5E74D2C921AA@microsoft.com...
> > > > > 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" <Mendz5@discussions.microsoft.com> wrote in message
> > > > > > news:F9441029-6722-494F-A541-EA5787C952A3@microsoft.com...
> > > > > > > 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),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)
> > > > > > > >
> > > > > > > > "Mendz5" <Mendz5@discussions.microsoft.com> wrote in message
> > > > > > > > news:0C2AE5BB-9456-44B5-A0EB-D516B591269F@microsoft.com...
> > > > > > > > > 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
> > > > > > > > >
> > > > > > > > > "Bob Phillips" wrote:
> > > > > > > > >
> > > > > > > > > >
> > > > > > > >
> > > > > >
> > > >
> >
=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" <Mendz5@discussions.microsoft.com> wrote in
message
> > > > > > > > > >
news:36FD65A9-2CEF-49D5-9B77-052054482DFE@microsoft.com...
> > > > > > > > > > > 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
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

[Mendz5]

Bob,

Thanks, you've been a great help!!

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" <Mendz5@discussions.microsoft.com> wrote in message
> news:97442693-F65B-49CE-84DA-E997222CB7CC@microsoft.com...
> > 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" <Mendz5@discussions.microsoft.com> wrote in message
> > > news:CFC0D88B-B3A1-4998-AFCC-E964223126D1@microsoft.com...
> > > > 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,M2:N2,Q2:R2))))
> > > > >
> > > > > --
> > > > > HTH
> > > > >
> > > > > Bob Phillips
> > > > >
> > > > > (replace somewhere in email address with gmail if mailing direct)
> > > > >
> > > > > "Mendz5" <Mendz5@discussions.microsoft.com> wrote in message
> > > > > news:DF60CCAE-A466-4D93-B0C1-5E74D2C921AA@microsoft.com...
> > > > > > 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" <Mendz5@discussions.microsoft.com> wrote in message
> > > > > > > news:F9441029-6722-494F-A541-EA5787C952A3@microsoft.com...
> > > > > > > > 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),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)
> > > > > > > > >
> > > > > > > > > "Mendz5" <Mendz5@discussions.microsoft.com> wrote in message
> > > > > > > > > news:0C2AE5BB-9456-44B5-A0EB-D516B591269F@microsoft.com...
> > > > > > > > > > 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
> > > > > > > > > >
> > > > > > > > > > "Bob Phillips" wrote:
> > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > >
> > > > > > >
> > > > >
> > >
> =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" <Mendz5@discussions.microsoft.com> wrote in
> message
> > > > > > > > > > >
> news:36FD65A9-2CEF-49D5-9B77-052054482DFE@microsoft.com...
> > > > > > > > > > > > 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,

[Mendz5]

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" 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" <Mendz5@discussions.microsoft.com> wrote in message
> news:97442693-F65B-49CE-84DA-E997222CB7CC@microsoft.com...
> > 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" <Mendz5@discussions.microsoft.com> wrote in message
> > > news:CFC0D88B-B3A1-4998-AFCC-E964223126D1@microsoft.com...
> > > > 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,M2:N2,Q2:R2))))
> > > > >
> > > > > --
> > > > > HTH
> > > > >
> > > > > Bob Phillips
> > > > >
> > > > > (replace somewhere in email address with gmail if mailing direct)
> > > > >
> > > > > "Mendz5" <Mendz5@discussions.microsoft.com> wrote in message
> > > > > news:DF60CCAE-A466-4D93-B0C1-5E74D2C921AA@microsoft.com...
> > > > > > 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" <Mendz5@discussions.microsoft.com> wrote in message
> > > > > > > news:F9441029-6722-494F-A541-EA5787C952A3@microsoft.com...
> > > > > > > > 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),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)
> > > > > > > > >
> > > > > > > > > "Mendz5" <Mendz5@discussions.microsoft.com> wrote in message
> > > > > > > > > news:0C2AE5BB-9456-44B5-A0EB-D516B591269F@microsoft.com...
> > > > > > > > > > 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
> > > > > > > > > >
> > > > > > > > > > "Bob Phillips" wrote:
> > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > >
> > > > > > >
> > > > >
> > >
> =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" <Mendz5@discussions.microsoft.com> wrote in
> message
> > > > > > > > > > >
> news:36FD65A9-2CEF-49D5-9B77-052054482DFE@microsoft.com...
> > > > > > > > > > > > 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,

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

"Mendz5" <Mendz5@discussions.microsoft.com> wrote in message
news:73E26CAC-10F4-41B3-9AF8-691F884A492F@microsoft.com...
> 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,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" 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" <Mendz5@discussions.microsoft.com> wrote in message
> > news:97442693-F65B-49CE-84DA-E997222CB7CC@microsoft.com...
> > > 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" <Mendz5@discussions.microsoft.com> wrote in message
> > > > news:CFC0D88B-B3A1-4998-AFCC-E964223126D1@microsoft.com...
> > > > > 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,M2:N2,Q2:R2))))
> > > > > >
> > > > > > --
> > > > > > HTH
> > > > > >
> > > > > > Bob Phillips
> > > > > >
> > > > > > (replace somewhere in email address with gmail if mailing
direct)
> > > > > >
> > > > > > "Mendz5" <Mendz5@discussions.microsoft.com> wrote in message
> > > > > > news:DF60CCAE-A466-4D93-B0C1-5E74D2C921AA@microsoft.com...
> > > > > > > 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" <Mendz5@discussions.microsoft.com> wrote in message
> > > > > > > > news:F9441029-6722-494F-A541-EA5787C952A3@microsoft.com...
> > > > > > > > > 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),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)
> > > > > > > > > >
> > > > > > > > > > "Mendz5" <Mendz5@discussions.microsoft.com> wrote in
message
> > > > > > > > > >
news:0C2AE5BB-9456-44B5-A0EB-D516B591269F@microsoft.com...
> > > > > > > > > > > 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
> > > > > > > > > > >
> > > > > > > > > > > "Bob Phillips" wrote:
> > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > >
> > > > > > > >
> > > > > >
> > > >
> >
=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" <Mendz5@discussions.microsoft.com> wrote in
> > message
> > > > > > > > > > > >
> > news:36FD65A9-2CEF-49D5-9B77-052054482DFE@microsoft.com...
> > > > > > > > > > > > > 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,

[Mendz5]

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" <Mendz5@discussions.microsoft.com> wrote in message
> news:73E26CAC-10F4-41B3-9AF8-691F884A492F@microsoft.com...
> > 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,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" 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" <Mendz5@discussions.microsoft.com> wrote in message
> > > news:97442693-F65B-49CE-84DA-E997222CB7CC@microsoft.com...
> > > > 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" <Mendz5@discussions.microsoft.com> wrote in message
> > > > > news:CFC0D88B-B3A1-4998-AFCC-E964223126D1@microsoft.com...
> > > > > > 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,M2:N2,Q2:R2))))
> > > > > > >
> > > > > > > --
> > > > > > > HTH
> > > > > > >
> > > > > > > Bob Phillips
> > > > > > >
> > > > > > > (replace somewhere in email address with gmail if mailing
> direct)
> > > > > > >
> > > > > > > "Mendz5" <Mendz5@discussions.microsoft.com> wrote in message
> > > > > > > news:DF60CCAE-A466-4D93-B0C1-5E74D2C921AA@microsoft.com...
> > > > > > > > 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" <Mendz5@discussions.microsoft.com> wrote in message
> > > > > > > > > news:F9441029-6722-494F-A541-EA5787C952A3@microsoft.com...
> > > > > > > > > > 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),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)
> > > > > > > > > > >
> > > > > > > > > > > "Mendz5" <Mendz5@discussions.microsoft.com> wrote in
> message
> > > > > > > > > > >
> news:0C2AE5BB-9456-44B5-A0EB-D516B591269F@microsoft.com...
> > > > > > > > > > > > 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.

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

"Mendz5" <Mendz5@discussions.microsoft.com> wrote in message
news:A4857DC2-E3E9-4C30-ADA4-FA2EC9FC93A8@microsoft.com...
> 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" <Mendz5@discussions.microsoft.com> wrote in message
> > news:73E26CAC-10F4-41B3-9AF8-691F884A492F@microsoft.com...
> > > 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,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" 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" <Mendz5@discussions.microsoft.com> wrote in message
> > > > news:97442693-F65B-49CE-84DA-E997222CB7CC@microsoft.com...
> > > > > 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" <Mendz5@discussions.microsoft.com> wrote in message
> > > > > > news:CFC0D88B-B3A1-4998-AFCC-E964223126D1@microsoft.com...
> > > > > > > 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,M2:N2,Q2:R2))))
> > > > > > > >
> > > > > > > > --
> > > > > > > > HTH
> > > > > > > >
> > > > > > > > Bob Phillips
> > > > > > > >
> > > > > > > > (replace somewhere in email address with gmail if mailing
> > direct)
> > > > > > > >
> > > > > > > > "Mendz5" <Mendz5@discussions.microsoft.com> wrote in message
> > > > > > > > news:DF60CCAE-A466-4D93-B0C1-5E74D2C921AA@microsoft.com...
> > > > > > > > > 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" <Mendz5@discussions.microsoft.com> wrote in
message
> > > > > > > > > >
news:F9441029-6722-494F-A541-EA5787C952A3@microsoft.com...
> > > > > > > > > > > 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),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)
> > > > > > > > > > > >
> > > > > > > > > > > > "Mendz5" <Mendz5@discussions.microsoft.com> wrote in
> > message
> > > > > > > > > > > >
> > news:0C2AE5BB-9456-44B5-A0EB-D516B591269F@microsoft.com...
> > > > > > > > > > > > > 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.

[Mendz5]

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" <Mendz5@discussions.microsoft.com> wrote in message
> news:A4857DC2-E3E9-4C30-ADA4-FA2EC9FC93A8@microsoft.com...
> > 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" <Mendz5@discussions.microsoft.com> wrote in message
> > > news:73E26CAC-10F4-41B3-9AF8-691F884A492F@microsoft.com...
> > > > 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,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" 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" <Mendz5@discussions.microsoft.com> wrote in message
> > > > > news:97442693-F65B-49CE-84DA-E997222CB7CC@microsoft.com...
> > > > > > 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" <Mendz5@discussions.microsoft.com> wrote in message
> > > > > > > news:CFC0D88B-B3A1-4998-AFCC-E964223126D1@microsoft.com...
> > > > > > > > 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,M2:N2,Q2:R2))))
> > > > > > > > >
> > > > > > > > > --
> > > > > > > > > HTH
> > > > > > > > >
> > > > > > > > > Bob Phillips
> > > > > > > > >
> > > > > > > > > (replace somewhere in email address with gmail if mailing
> > > direct)
> > > > > > > > >
> > > > > > > > > "Mendz5" <Mendz5@discussions.microsoft.com> wrote in message
> > > > > > > > > news:DF60CCAE-A466-4D93-B0C1-5E74D2C921AA@microsoft.com...
> > > > > > > > > > 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" <Mendz5@discussions.microsoft.com> wrote in
> message
> > > > > > > > > > >
> news:F9441029-6722-494F-A541-EA5787C952A3@microsoft.com...
> > > > > > > > > > > > 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

[Bob Phillips]

Adds 0 if both dates are not present.

--
HTH

Bob Phillips

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

"Mendz5" <Mendz5@discussions.microsoft.com> wrote in message
news:1AB04444-BA5C-46C5-AA96-4015F70DA01E@microsoft.com...
> 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" <Mendz5@discussions.microsoft.com> wrote in message
> > news:A4857DC2-E3E9-4C30-ADA4-FA2EC9FC93A8@microsoft.com...
> > > 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" <Mendz5@discussions.microsoft.com> wrote in message
> > > > news:73E26CAC-10F4-41B3-9AF8-691F884A492F@microsoft.com...
> > > > > 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,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" 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" <Mendz5@discussions.microsoft.com> wrote in message
> > > > > > news:97442693-F65B-49CE-84DA-E997222CB7CC@microsoft.com...
> > > > > > > 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" <Mendz5@discussions.microsoft.com> wrote in message
> > > > > > > > news:CFC0D88B-B3A1-4998-AFCC-E964223126D1@microsoft.com...
> > > > > > > > > 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,M2:N2,Q2:R2))))
> > > > > > > > > >
> > > > > > > > > > --
> > > > > > > > > > HTH
> > > > > > > > > >
> > > > > > > > > > Bob Phillips
> > > > > > > > > >
> > > > > > > > > > (replace somewhere in email address with gmail if
mailing
> > > > direct)
> > > > > > > > > >
> > > > > > > > > > "Mendz5" <Mendz5@discussions.microsoft.com> wrote in
message
> > > > > > > > > >
news:DF60CCAE-A466-4D93-B0C1-5E74D2C921AA@microsoft.com...
> > > > > > > > > > > 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" <Mendz5@discussions.microsoft.com> wrote in
> > message
> > > > > > > > > > > >
> > news:F9441029-6722-494F-A541-EA5787C952A3@microsoft.com...
> > > > > > > > > > > > > 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

[Mendz5]

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),MAX(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" <Mendz5@discussions.microsoft.com> wrote in message
> news:1AB04444-BA5C-46C5-AA96-4015F70DA01E@microsoft.com...
> > 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" <Mendz5@discussions.microsoft.com> wrote in message
> > > news:A4857DC2-E3E9-4C30-ADA4-FA2EC9FC93A8@microsoft.com...
> > > > 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" <Mendz5@discussions.microsoft.com> wrote in message
> > > > > news:73E26CAC-10F4-41B3-9AF8-691F884A492F@microsoft.com...
> > > > > > 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,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" 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" <Mendz5@discussions.microsoft.com> wrote in message
> > > > > > > news:97442693-F65B-49CE-84DA-E997222CB7CC@microsoft.com...
> > > > > > > > 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" <Mendz5@discussions.microsoft.com> wrote in message
> > > > > > > > > news:CFC0D88B-B3A1-4998-AFCC-E964223126D1@microsoft.com...
> > > > > > > > > > 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,M2:N2,Q2:R2))))
> > > > > > > > > > >
> > > > > > > > > > > --
> > > > > > > > > > > HTH
> > > > > > > > > > >
> > > > > > > > > > > Bob Phillips
> > > > > > > > > > >
> > > > > > > > > > > (replace somewhere in email address with gmail if
> mailing
> > > > > direct)
> > > > > > > > > > >
> > > > > > > > > > > "Mendz5" <Mendz5@discussions.microsoft.com> wrote in
> message
> > > > > > > > > > >
> news:DF60CCAE-A466-4D93-B0C1-5E74D2C921AA@microsoft.com...
> > > > > > > > > > > > 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
> > > > > > > > > > > > >

[Bob Phillips]

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" <Mendz5@discussions.microsoft.com> wrote in message
news:69C0BD02-3475-468E-A00B-57072A00A429@microsoft.com...
> 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),MAX(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" <Mendz5@discussions.microsoft.com> wrote in message
> > news:1AB04444-BA5C-46C5-AA96-4015F70DA01E@microsoft.com...
> > > 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" <Mendz5@discussions.microsoft.com> wrote in message
> > > > news:A4857DC2-E3E9-4C30-ADA4-FA2EC9FC93A8@microsoft.com...
> > > > > 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" <Mendz5@discussions.microsoft.com> wrote in message
> > > > > > news:73E26CAC-10F4-41B3-9AF8-691F884A492F@microsoft.com...
> > > > > > > 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,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" 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" <Mendz5@discussions.microsoft.com> wrote in message
> > > > > > > > news:97442693-F65B-49CE-84DA-E997222CB7CC@microsoft.com...
> > > > > > > > > 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" <Mendz5@discussions.microsoft.com> wrote in
message
> > > > > > > > > >
news:CFC0D88B-B3A1-4998-AFCC-E964223126D1@microsoft.com...
> > > > > > > > > > > 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,M2:N2,Q2:R2))))
> > > > > > > > > > > >
> > > > > > > > > > > > --
> > > > > > > > > > > > HTH
> > > > > > > > > > > >
> > > > > > > > > > > > Bob Phillips
> > > > > > > > > > > >
> > > > > > > > > > > > (replace somewhere in email address with gmail if
> > mailing
> > > > > > direct)
> > > > > > > > > > > >
> > > > > > > > > > > > "Mendz5" <Mendz5@discussions.microsoft.com> wrote in
> > message
> > > > > > > > > > > >
> > news:DF60CCAE-A466-4D93-B0C1-5E74D2C921AA@microsoft.com...
> > > > > > > > > > > > > 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
> > > > > > > > > > > > > >

[Mendz5]

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" <Mendz5@discussions.microsoft.com> wrote in message
> news:69C0BD02-3475-468E-A00B-57072A00A429@microsoft.com...
> > 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),MAX(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" <Mendz5@discussions.microsoft.com> wrote in message
> > > news:1AB04444-BA5C-46C5-AA96-4015F70DA01E@microsoft.com...
> > > > 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" <Mendz5@discussions.microsoft.com> wrote in message
> > > > > news:A4857DC2-E3E9-4C30-ADA4-FA2EC9FC93A8@microsoft.com...
> > > > > > 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" <Mendz5@discussions.microsoft.com> wrote in message
> > > > > > > news:73E26CAC-10F4-41B3-9AF8-691F884A492F@microsoft.com...
> > > > > > > > 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,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" 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" <Mendz5@discussions.microsoft.com> wrote in message
> > > > > > > > > news:97442693-F65B-49CE-84DA-E997222CB7CC@microsoft.com...
> > > > > > > > > > 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" <Mendz5@discussions.microsoft.com> wrote in
> message
> > > > > > > > > > >
> news:CFC0D88B-B3A1-4998-AFCC-E964223126D1@microsoft.com...
> > > > > > > > > > > > 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,M2:N2,Q2:R2))))
> > > > > > > > > > > > >
> > > > > > > > > > > > > --
> > > > > > > > > > > > > HTH
> > > > > > > > > > > > >
> > > > > > > > > > > > > Bob Phillips
> > > > > > > > > > > > >
> > > > > > > > > > > > > (replace somewhere in email address with gmail if
> > > mailing
> > > > > > > direct)
> > > > > > > > > > > > >
> > > > > > > > > > > > > "Mendz5" <Mendz5@discussions.microsoft.com> wrote in
> > > message
> > > > > > > > > > > > >
> > > news:DF60CCAE-A466-4D93-B0C1-5E74D2C921AA@microsoft.com...
> > > > > > > > > > > > > > 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

[Mendz5]

Bob,

Forgot to mention the following:

If A1 and B1 are the same date and C1 is blank, then the result should be 1
day duration.

The same goes if A1, B1 and C1 are the same date the result should be 1 day
duration.

If A1 and C1 are the same date and B1 is blank, the result should be 1 day
duration.

And finally, If B1 and C1 are the same date and A1 is blank the result
should be 1 day duration.

Sorry for this mess :-(

Thank,

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" <Mendz5@discussions.microsoft.com> wrote in message
> news:69C0BD02-3475-468E-A00B-57072A00A429@microsoft.com...
> > 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),MAX(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" <Mendz5@discussions.microsoft.com> wrote in message
> > > news:1AB04444-BA5C-46C5-AA96-4015F70DA01E@microsoft.com...
> > > > 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" <Mendz5@discussions.microsoft.com> wrote in message
> > > > > news:A4857DC2-E3E9-4C30-ADA4-FA2EC9FC93A8@microsoft.com...
> > > > > > 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" <Mendz5@discussions.microsoft.com> wrote in message
> > > > > > > news:73E26CAC-10F4-41B3-9AF8-691F884A492F@microsoft.com...
> > > > > > > > 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,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" 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" <Mendz5@discussions.microsoft.com> wrote in message
> > > > > > > > > news:97442693-F65B-49CE-84DA-E997222CB7CC@microsoft.com...
> > > > > > > > > > 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" <Mendz5@discussions.microsoft.com> wrote in
> message
> > > > > > > > > > >
> news:CFC0D88B-B3A1-4998-AFCC-E964223126D1@microsoft.com...
> > > > > > > > > > > > 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,M2:N2,Q2:R2))))
> > > > > > > > > > > > >
> > > > > > > > > > > > > --
> > > > > > > > > > > > > HTH
> > > > > > > > > > > > >
> > > > > > > > > > > > > Bob Phillips
> > > > > > > > > > > > >
> > > > > > > > > > > > > (replace somewhere in email address with gmail if
> > > mailing
> > > > > > > direct)
> > > > > > > > > > > > >
> > > > > > > > > > > > > "Mendz5" <Mendz5@discussions.microsoft.com> wrote in
> > > message
> > > > > > > > > > > > >
> > > news:DF60CCAE-A466-4D93-B0C1-5E74D2C921AA@microsoft.com...
> > > > > > > > > > > > > > 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

[Bob Phillips]

=IF(COUNTIF(A1:C1,"<>")<2,"N/A",IF(MIN(A1:C1)=MAX(A1:C1),1,NETWORKDAYS(MIN(A
1:C1)+1,MAX(A1:C1))))

--
HTH

Bob Phillips

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

"Mendz5" <Mendz5@discussions.microsoft.com> wrote in message
news:18EF09C7-F569-4860-9F9B-4B21FF3D35CA@microsoft.com...
> Bob,
>
> Forgot to mention the following:
>
> If A1 and B1 are the same date and C1 is blank, then the result should be
1
> day duration.
>
> The same goes if A1, B1 and C1 are the same date the result should be 1
day
> duration.
>
> If A1 and C1 are the same date and B1 is blank, the result should be 1 day
> duration.
>
> And finally, If B1 and C1 are the same date and A1 is blank the result
> should be 1 day duration.
>
> Sorry for this mess :-(
>
> Thank,
>
> 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" <Mendz5@discussions.microsoft.com> wrote in message
> > news:69C0BD02-3475-468E-A00B-57072A00A429@microsoft.com...
> > > 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),MAX(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" <Mendz5@discussions.microsoft.com> wrote in message
> > > > news:1AB04444-BA5C-46C5-AA96-4015F70DA01E@microsoft.com...
> > > > > 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" <Mendz5@discussions.microsoft.com> wrote in message
> > > > > > news:A4857DC2-E3E9-4C30-ADA4-FA2EC9FC93A8@microsoft.com...
> > > > > > > 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" <Mendz5@discussions.microsoft.com> wrote in message
> > > > > > > > news:73E26CAC-10F4-41B3-9AF8-691F884A492F@microsoft.com...
> > > > > > > > > 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,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" 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" <Mendz5@discussions.microsoft.com> wrote in
message
> > > > > > > > > >
news:97442693-F65B-49CE-84DA-E997222CB7CC@microsoft.com...
> > > > > > > > > > > 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" <Mendz5@discussions.microsoft.com> wrote in
> > message
> > > > > > > > > > > >
> > news:CFC0D88B-B3A1-4998-AFCC-E964223126D1@microsoft.com...
> > > > > > > > > > > > > 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,M2:N2,Q2:R2))))
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > --
> > > > > > > > > > > > > > HTH
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > Bob Phillips
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > (replace somewhere in email address with gmail
if
> > > > mailing
> > > > > > > > direct)
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > "Mendz5" <Mendz5@discussions.microsoft.com>
wrote in
> > > > message
> > > > > > > > > > > > > >
> > > > news:DF60CCAE-A466-4D93-B0C1-5E74D2C921AA@microsoft.com...
> > > > > > > > > > > > > > > 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