Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Apr 2006
    Location
    Accra, Ghana
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date Calculation error (Excel 2003)

    Hi

    I am trying to use the WORKDAY and NETWORKDAYS function.

    I attach a small workbook where I explain the problem in every line.

    In short if I use the WORKDAY function to calculate a future date and then use the NETWORKDAYS function to calculate the working days involved I do not always get the same answer.

    Am I using the functions wrong?

    Help and comments will be appreciated
    Attached Files Attached Files

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

    Re: Date Calculation error (Excel 2003)

    The WORKDAY function computes the n-th working day after the start date. The formula =WORKDAY(A1,1,Holidays) returns the 1st working day after the date in A1, taking weekends and the Holidays range into account.
    The NETWORKDAYS function computes the number of working days within the date range from the start date to the end date. Both the start date and the end date are included in the count if they are working days.

    So in your example in row 17:
    B17 = 4-Jul-08
    C17 = 1
    D17 contains the formula =WORKDAY(B16,C16,holiday1). This computes the 1st working day after 4-Jul-08. Since 5-Jul-08 and 6-Jul-08 are weekend days, the result is 7-Jul-08.
    F17 contains the formula =NETWORKDAYS(B17,D17,holiday1). Start date = 4-Jul-08, end date = 7-Jul-08. There are 2 working days in this range (the start and end date), and 2 weekend days. So the result is 2.

    Conclusion: The functions return the correct result according to their definitions. You may not agree with the definitions, but that's another matter.

  3. #3
    Star Lounger
    Join Date
    Apr 2006
    Location
    Accra, Ghana
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Calculation error (Excel 2003)

    Hans

    Thank you for the answer. I was trying to put together a speadsheet where you can split a budget over time into columns and at the same time calculate the end dates of activities.

    I attach again a small workbook to illustrate the problem with the different logic of the functions.

    Can you perhaps suggest a better way or a solution?

    Regards
    Attached Files Attached Files

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

    Re: Date Calculation error (Excel 2003)

    You can use the following formula in G2 to determine the end date:

    =WORKDAY(D2,E2-AND(WEEKDAY(D2,2)<6,ISERROR(MATCH(D2,holiday,0))), holiday)

    and fill down. This will subtract 1 day from the end date if the start date is a working day.

  5. #5
    Star Lounger
    Join Date
    Apr 2006
    Location
    Accra, Ghana
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Calculation error (Excel 2003)

    Hans

    I was trying something similar before but in a much less sophisticated way, it just partly solved the problem.

    Your formula is just perfect as always.

    Thank you very much.

    Have a nice weekend

    Regards

Posting Permissions

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