Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am finding my formula array for NETWORKDAYS to be inadequate.

    SUM(A2:B2)+(B2-NETWORKDAYS(A2,SUM(A2+B2),A5:A28))

    Something is missing since I test various workdays {0,1,2,3,4,5,6,7,8,9,10} and don't get a consistent answer. I've been playing around with including an IF statement in the array IF(NETWORKDAYS(A2,SUM(A2+B2),A5:A28))<B2,SUM(A2:B2 )+B2-NETWORKDAYS(A2,SUM(A2+B2),A5:A28),SUM(A2+B2)), but that is generating an error and I can't figure out why.

    I have a Start Date (A2) and number of work days (B2) that are entered in a spreadsheet. I want the End Date (D2) calculated by adding the two values, but I don't want the number of work days to include weekends or the list of holidays that I created.

    The attached spreadsheet has my calculations.

    Any help is always appreciated.

    Amy
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Does the following do what you want?

    =WORKDAY(A2,B2,A5:A28)

    You need to activate the Analysis ToolPak add-in for this in Excel 97-2003. See WORKDAY

  3. #3
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='AmyN' post='777333' date='28-May-2009 17:45']I am finding my formula array for NETWORKDAYS to be inadequate.

    SUM(A2:B2)+(B2-NETWORKDAYS(A2,SUM(A2+B2),A5:A28))

    Something is missing since I test various workdays {0,1,2,3,4,5,6,7,8,9,10} and don't get a consistent answer. I've been playing around with including an IF statement in the array IF(NETWORKDAYS(A2,SUM(A2+B2),A5:A28))<B2,SUM(A2:B2 )+B2-NETWORKDAYS(A2,SUM(A2+B2),A5:A28),SUM(A2+B2)), but that is generating an error and I can't figure out why.

    I have a Start Date (A2) and number of work days (B2) that are entered in a spreadsheet. I want the End Date (D2) calculated by adding the two values, but I don't want the number of work days to include weekends or the list of holidays that I created.

    The attached spreadsheet has my calculations.

    Any help is always appreciated.

    Amy[/quote]
    I just answered my own question. It is not NETWORKDAY that should be used, it is the WORKDAY function

    =WORKDAY(A2,B2,A5:A28)

    Thanks
    Amy

  4. #4
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='777347' date='28-May-2009 19:17']Does the following do what you want?

    =WORKDAY(A2,B2,A5:A28)

    You need to activate the Analysis ToolPak add-in for this in Excel 97-2003. See WORKDAY[/quote]
    Hans,
    You beat me to it just as I figured it out and posted the same answer.

    Thank you.
    Amy

Posting Permissions

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