Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Workdays Discrepancy (Excel 2003, SP1)

    Could someone please tell me why this is happening and how to fix it? A file illustrating the problem is attached. Basically, this is what is happening:

    9/5/05 + 200 work days = 6/19/06
    Networkdays(9/5/05, 6/19/06) = 200 days

    8/9/05 + 200 workdays = 5/23/06
    Networkdays(8/9/05, 5/23/06) = <span style="background-color: #FFFF00; color: #000000; font-weight: bold">201 days</span hi>

    I suspect it has something to do with precision or maybe the date formatting, but I haven't had time yet to start experimenting. I'm hoping it is a known phenomenon, so I can just fix the problem instead of continuing to try to figure out what is causing the problem.

    Many thanks!

    --Karyl

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Workdays Discrepancy (Excel 2003, SP1)

    NETWORKDAYS includes the start and end dates in the count if they are not weekend days or holidays.
    So if A1 contains 10-Aug-05 (a Wednesday) and B1 contains 11-Aug-05 (a Thursday), the formula =NETWORKDAYS(A1,B1,Holidays) returns 2.

    WORKDAY returns a date the specified number of working days after the start date, i.e. it doesn't include the start date in the count.
    If A1 contains 10-Aug-05 (a Wednesday), the formula =WORKDAY(A1,2,Holidays) returns 12-Aug-05 (a Friday)

    As a consequence, there will normally be an apparent difference of 1 day between the results of NETWORKDAYS and WORKDAY. This is what you see in your second example. In your first example, the start date 05-Sep-05 is a holiday (Labor Day), so it is NOT included in the count returned by NETWORKDAYS, and hence the result is one less than "normally".

  3. #3
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workdays Discrepancy (Excel 2003, SP1)

    Many thanks! I was looking so hard, I didn't see the obvious. I've now modified my code to check to prevent users from setting start dates on holidays as well as Saturdays and Sundays.

    --Karyl

Posting Permissions

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