NETWORKDAYS calc with 3 columns of dates

Mendz5 · 08-10-2006, 05:50 PM

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 · 08-10-2006, 06:05 PM

=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 · 08-10-2006, 06:55 PM

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 · 08-10-2006, 07:30 PM

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 · 08-11-2006, 08:25 AM

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 · 08-11-2006, 09:25 AM

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

Mark · 08-10-2006, 06:10 PM

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 · 08-10-2006, 07:00 PM

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