NETWORKDAYS and WORKDAY Discrepancy

Remy L

Feel free to duplicate this yourself to see the problem:

I am using manually created durations in order to calculate the finish date and determine if a task could run late if it takes as long as forecasted.

The formula in Est. Duration is:

=NETWORKDAYS([Est. Start]@row, [Est. Finish]@row)

The formula in estDuration is:

=[Est. Duration]@row

The formula in calcFinish is:

=WORKDAY([Start Date]@row, VALUE(estDuration@row))

I need the helper for estDuration in order to convert Est. Duration to a number.

But if you look at the values, they are inconsistent. I understand that NETWORKDAYS includes the start date. In that case, "27" makes sense. But in going back the other way, with WORKDAY I should subtract 1.

However, look at the second row. It calculates the duration correctly AND it calculate the finish date correctly going back the other way.

Help!!!

KPH

The discrepancy is because you have non work days included in your start and end dates.

Row 1 start date is a Tuesday and is included in the networkdays between then and finish date of March 13, also a Tuesday. Adding networkdays to start date therefore gives you the finish date plus 1. As expected.

Row 2 start date is a Sunday, so does not get included in networkdays. Therefore there are only 3 working days between start and finish. 3 working days after Sunday is Monday 26, Tuesday 27, Wednesday 28. Adding networkdays to the start gives you the original finish date (no plus 1 as start date was not counted).

Row 3 start date is a Wednesday so is included in networkdays (like row 1). However, the finish day is a Saturday so not included. Adding the networkdays to the start date will therefore give you the original finish date as in row 2 (no plus 1, as the finishing Saturday is not included). However, as that is a Saturday the formula returns the next working day which is the Monday (11th).

Row 4 follows the same pattern as row 1.