+ Reply to Thread
Results 1 to 27 of 27

NETWORKDAYS calc with 3 columns of dates

Hybrid View

  1. #1
    Mendz5
    Guest

    NETWORKDAYS calc with 3 columns of dates

    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

  2. #2
    Bob Phillips
    Guest

    Re: NETWORKDAYS calc with 3 columns of dates

    =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




  3. #3
    Mendz5
    Guest

    Re: NETWORKDAYS calc with 3 columns of dates

    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

    >
    >
    >


  4. #4
    Bob Phillips
    Guest

    Re: NETWORKDAYS calc with 3 columns of dates

    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

    > >
    > >
    > >




  5. #5
    Mendz5
    Guest

    Re: NETWORKDAYS calc with 3 columns of dates

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

    >
    >
    >


  6. #6
    Bob Phillips
    Guest

    Re: NETWORKDAYS calc with 3 columns of dates

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

    > >
    > >
    > >




  7. #7
    Mark
    Guest

    Re: NETWORKDAYS calc with 3 columns of dates


    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


  8. #8
    Mendz5
    Guest

    Re: NETWORKDAYS calc with 3 columns of dates

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


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1