Confused by NETWORKDAYS results

I have a formula such as:

=NETWORKDAYS([Planned Start]1, [Actual Start]1)

When the Planned Start date is say 10/29/14, and the Actual Start date is 10/30/14, the result is 2. To me, the difference between those two dates is one day, not two. I don't have any holidays set for these dates, and I've tried wrapping Actual Start in a DATEONLY expression, just in case there's a time component to this calculation, and I'm getting weird diff based on one column being date/time, and another just date. Adding the DATEONLY expression doesn't affect the output...I still get a 2 returned. Any insight into either what I'm doing wrong, why 2 is the right answer, or to get this to return a value of 1?

For a little background, I'm building out a column that'll show me how tasks are tracking compared to their original dates.

Find more posts tagged with

Formulas

Comments

Gene Hurley

I should add that a formula like this would/does return the expected value of 1 day when the dates are 10/29/14, and 10/30/14:

=DATEONLY([Planned Start]1) - DATEONLY([Actual Start]1)

But - that formula doesn't pay attention to weekends or holidays in its calculation.

Zack S

Hello Gene,

The working day calculations will include each day as a working day rather than showing the difference between to dates. In your example it is including all of the 29th as a day and all of the 30th as a day giving you two working days. As we have partial day durations, it takes the entire day into account depending on when the task starts and how much of the day it takes up.

The DATEONLY formula is designed to not take into account the actual task, but just the date information that is displayed, which is why you get a difference of one day in that case. It doesn’t take into consideration working days because it is designed to do the math between the actual dates and not factor the actual task linked to each date which is what is utilizing the working day settings.

As a workaround, you can use the same formula you mention for NETWORKDAYS, and add a -1 at the end, and this will adjust for the slippage you are looking for. For example your formula would appear as such: =NETWORKDAYS([Planned Start]1, [Actual Start]1)-1.

-Zack

Gene Hurley

Thanks Zack! I'd tried that permutation - it works as expected when the Actual Start is later than the Planned Start, but gets wonky when the Actual comes in sooner than the Planned. I'd mistakenly hoped/thought that a function like NETWORKDAYs would do this for me, but your explanation perfectly clarifies what it is actually doing.

So, with that, I did what I was hoping to avoid - wrapped the calculations in some IF/THEN logic that looks to see if the NETWORKDAYS calculation returns positive or negative results, then does the +/- day adjustment accordingly. Multiplied the result by -1 because I prefer the 'late' values to show as negative. Looks to work as I'd hoped, but not exactly pretty.

=IF(NETWORKDAYS([Planned Start]1, [Actual Start]1) < 0, NETWORKDAYS([Planned Start]1, [Actual Start]1) + 1, NETWORKDAYS([Planned Start]1, [Actual Start]1) - 1) * -1

John Sauber

Hi Gene,

We had a very similar need. This discussion should set you on your path.

Let me know how this works for you.

Gene Hurley

I think the formula I pasted in my previous post is what I was looking to do (but was looking for a simpler way to achieve it). I was looking for a "baseline" function, similar to what was discussed in https://community.smartsheet.com/discussion/baseline-functionality

Following the ideas posted there, I created the Planned Start/End columns in my Smartsheet. After filling out all my project plan's Actual dates for each task, I copied and pasted the dates out of the Actual columns, and pasted them into my Planned date columns (I now have my 'baseline'). Added my calculated column from above that will show my how my Actuals are matching up against the plan. My goal here is to be able to help answer to the management question - "why is my project late?". If I can show where tasks deviated from the original plan, I can point to specific items. If (a big if) I put a comment on tasks indicating why the dates changed, I'd have a pretty solid audit trail to go back to.

To go back to your post, the CALCDURATION function is comparing one task to a subsequent one, whereas what I was looking for is that comparison to be self contained within a single row. You also commented that CALCDURATION only works on Date/Time columns. My Planned dates have a type of 'Date'. I still don't get why Smartsheet is so strict about having only one set of Date/Time columns per sheet, but that's a story for anothery day. I did try using CALCDURATION comparing actual and planned in a single row, but the results were slightly off - I'm certain that has to do with calculating durations/partial days. But the bigger thing it's missing is that it doesn't show if I'm 100 days early, or 100 days late. Just get a result of 100, so I'd still need to wrap some additional logic around the calculation to determine that.