Networkdays formula not behaving as expected

Stewart Ennew

Hi I'm having an issue with NETWORKDAYS not behaving as expected, I'm trying to report project timeline variances between baseline and actual. I have defined working days and holidays but the results are not as expected, any thoughts?

• I'm using this formula in the Project Start var. column a =NETWORKDAYS([Actual Start Date]4, [Baseline Start Date]4)
• and this formula in the Project End var. =NETWORKDAYS([Actual End Date]4, [Baseline End Date]4)
• January the 1st is set as a holiday in the template settings and weekends are non working days. Line 3 looks right but line 4 is adding a day tot he total for each column

Paul Newcome

Think of it more as a duration instead of a difference between dates.

If your start and end dates are the same, then the result is one day which has an output of 1 instead of 0 because from start to finish took 1 day.

Row 3 only LOOKS correct based on your logic, but that is because Jan 1 is a holiday and is therefore not counted. The formula is changing Jan 1 to Jan 2. If Jan 1 were included as a "non-holiday", the formula would have generated a -2.

I am not sure what is happening though on rows 5, 6, and 7 to generate a zero.

If you would like to adjust the output to reflect a date difference instead of a duration, then you can use an IF statement to add either 1 or -1 depending on which way the dates are going.

=IF([Actual End Date]@row> [Baseline End Date]@row, 1, -1)

Then add it to your current formula and you should have the results you are looking for...

=NETWORKDAYS([Actual End Date]@row, [Baseline End Date]@row) + IF([Actual End Date]@row > [Baseline End Date]@row, 1, -1)

Pierre Galiegue

For what I see, there not any issue. Maybe did I not get a well understanding ...

Let me explain my point of view :

First of all, NETWORKDAYS(Date1, Date2) evaluate the number of days from Date1 to Date2, including both, excluding holiday and weekends.

On line 3, col Project Start var., based on your formula, you will get the number of days from 2-jan-2020 to 1-jan-2020 : -1 as you are going backward from the 2nd to the 1st of january (and 1st is a closed day)

And same way of thinking on the other cells.

Maybe did you confuse on the order in the NETWORKDAYS' settings. The first one is the date on which you will start to count, until you reach the 2nd date. In your case, you count backward.

Stewart Ennew

Pierre, thank you for your response, you are correct line 3 performs as expected, however if you look at line 4 the dates are 3-Jan-2020 to 2-Jan-2020 so i would expect -1 as a result, however it is giving me -2.

The dates are both working days an not holidays so i do not understand why one line works and the next line does not

thank you in advance

Paul Newcome

Think of it more as a duration instead of a difference between dates.

If your start and end dates are the same, then the result is one day which has an output of 1 instead of 0 because from start to finish took 1 day.

Row 3 only LOOKS correct based on your logic, but that is because Jan 1 is a holiday and is therefore not counted. The formula is changing Jan 1 to Jan 2. If Jan 1 were included as a "non-holiday", the formula would have generated a -2.

I am not sure what is happening though on rows 5, 6, and 7 to generate a zero.

If you would like to adjust the output to reflect a date difference instead of a duration, then you can use an IF statement to add either 1 or -1 depending on which way the dates are going.

=IF([Actual End Date]@row> [Baseline End Date]@row, 1, -1)

Then add it to your current formula and you should have the results you are looking for...

=NETWORKDAYS([Actual End Date]@row, [Baseline End Date]@row) + IF([Actual End Date]@row > [Baseline End Date]@row, 1, -1)

Stewart Ennew

Paul,

right ok that makes sense, I think I got hung up on the adding a day in for weekends or holidays function and it blinded me to my obvious mistake (Jan 1). Just tested the formula and it works as i would expect, much appreciated

Paul Newcome

Happy to help! 👍️

ChelseaH

I was having a similar problem and didn't consider that it would work like a duration (counting the first and last date).

I got around it by creating an IF formula:

=IF(NETWORKDAYS([First Date]@row, [Second Date]@row) > 0, NETWORKDAYS([First Date]@row, [Second Date]@row) - 1, NETWORKDAYS([First Date]@row, [Second Date]@row) + 1)

This enabled it to return 1, 0, and -1 as expected if counting number of days between two dates.