Consistency of NETWORKDAYS and WORKDAY

**6StringJazzer** · 10-01-2014, 09:29 PM

I am developing an Excel project scheduler that mimics some Microsoft Project functionality. If the user enters Start and Finish dates, it uses NETWORKDAYS to calculate Duration based on Start and Finish dates. Similarly, if the user enters Start and Duration, it uses WORKDAY to calculate a Finish date based on Start and Duration. (This is implemented in VBA, so it detects which two values the user has entered.)

The problem is that the functions aren't consistent.

Start	Finish	Duration calculated with NETWORKDAYS	Finish recalculated using WORKDAY
Wed 10/1/2014	Thu 10/2/2014	2	Fri 10/3/2014

Am I simply incorrectly interpreting the specs for these two functions? I can certainly fudge the results by adding or subtracting 1 here and there but I would rather make sure I understand how they're supposed to work. (I have never been impressed with the rigor of Microsoft's documentation of Excel functions but that is a discussion for another day.)

"Even Moderators Get The Blues." --apologies to Tom Robbins

**sktneer** · 10-02-2014, 12:02 AM

I don't know where is the confusion.
But Networkdays return the number of whole workdays between two dates. Like

=NETWORKDAYS("1/10/2014","02/10/2014") will return 2 as there are two workdays betweej 01/10/2014 (dd/mm/yyyy) and 02/10/2014 including the start date and end date.

While Workdays returns the date before of after (if depends whether you input the days in the past or future with - or + (default) sign) a specified number of workdays. Like

=WORKDAY("01/10/2014",2) will return 03/10/2014 i.e. two workdays after the date 01/10/2014. i.e. 01/10/2014 being the Wednesday, Thursday and Friday two workdays after Wednesday so returns the date on Friday.

and if you use the following formula
=WORKDAY("03/10/2014",2) Where 03/10/2014 if Friday. So it calculates like this.....

03/10/2014 Friday
04/10/2014 Saturday (not counted)
05/10/2014 Sunday (not counted)
06/10/2014 Monday (first count)
07/10/2014 Tuesday (second count)

Therefore =WORKDAY("03/10/2014",2) will return 07/10/2014 i.e. the first working day after 03/10/2014 is 06/10/2014 and the second working day after 03/10/2014 will be 07/10/2014.

Forgive me if I was not able to understand your question.

**6StringJazzer** · 10-02-2014, 08:34 AM

So to summarize your explanation:

NETWORKDAYS calculates the number of working days between the two dates INCLUSIVE. The count includes the start and end days (in the case where they are both working days).
WORKDAY calculates a date after a number of working days EXCLUSIVE. The count does not include the start date (in the case where the start date is a working day).

I am not confused in the least. My frustration is that these two functions are so closely related, they should be defined to be complementary but based on your explanation, they are not. I need to use complementary calculations as described in my opening post, but I can't use these functions alone. I have to add arithmetic to account for the fact that they are not complementary.

I need to calculate a Finish date using Start and Duration such that I can also calculate Duration from the Start and Finish and get the same answer. Using WORKDAY I can't simply subtract 1 from the result, because that could yield a non-working day. It appears that I can get the correct answer by subtracting one from the days argument, although I haven't done enough tests to confirm that will work in all cases.

**sktneer** · 10-02-2014, 09:01 AM

Yes you are correct. Subtracting 1 from the final result may return non-working day. Rather subtracting 1 in the days will return the the workday.
Like if A1 = 02/10/2014 (dd/mm/yyyy) (Thursday)
=WORKDAY(A1,1) will return 03/10/2014 (Friday)
and
=WORKDAY(A1,2)-1 will return 05/10/2014 (Sunday), which is wrong result.
So if you are using VBA to calculate this, you can pass the days - 1 input by the user in the formula.