Networkdays formula not behaving as expected
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
Best Answer

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 "nonholiday", 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)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!
Answers

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 2jan2020 to 1jan2020 : 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.

Pierre, thank you for your response, you are correct line 3 performs as expected, however if you look at line 4 the dates are 3Jan2020 to 2Jan2020 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

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 "nonholiday", 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)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!

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

Happy to help! 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!

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.
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.9K Get Help
 379 Global Discussions
 212 Industry Talk
 442 Announcements
 4.6K Ideas & Feature Requests
 140 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 302 Events
 33 Webinars
 7.3K Forum Archives