Date Calculation error (Excel 2003)

[kobusl]

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

[HansV]

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.

[kobusl]

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

[HansV]

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.

[kobusl]

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