Variance Column Showing 1.. Needs to be 0! Help!

Hi all - I am working on a variance formula to first.. if error make a blank... and then to secondly... calculate the number of working days between to dates. I need the variance of our "planned end" and "actual end" columns. I have the formula just about right! But it is producing a 1 instead of zero when the dates match. Any suggestions on how to fix my formula?

=IFERROR(NETWORKDAYS([Planned End]29, [Actual End]29), "")

See screenshot below.

SS Variance Formula.JPG

Find more posts tagged with

Project Management

Comments

Mike Wilday

Hmmm. Odd. I am getting 1 when two dates are equal and it seems to be consistent. If I go a day early I get -2. Because the formula is always adding a full day, try this one:

=IF(NETWORKDAYS([Planned End]29, [Actual End]29) < 0, NETWORKDAYS([Planned End]29, [Actual End]29) + 1, IF(NETWORKDAYS([Planned End]29, [Actual End]29) > 0, NETWORKDAYS([Planned End]29, [Actual End]29) - 1))

That will return a 0 result on same day or the correct negative or plus result.

Bri Boyer

That looks like it's working! Now I need it to stay blank and not #INVALID DATA TYPE when the dates aren't inputted!

Thanks so much Mike!

Mike Wilday

You can wrap the whole thing in an =IFERROR functions.

=IFERROR(IF(NETWORKDAYS([Planned End]29, [Actual End]29) < 0, NETWORKDAYS([Planned End]29, [Actual End]29) + 1, IF(NETWORKDAYS([Planned End]29, [Actual End]29) > 0, NETWORKDAYS([Planned End]29, [Actual End]29) - 1)),"")

Bri Boyer

Yes thank you! One last thing - would I use the same formula in the parent rows? It doesn't appear to be calculating all variances below it.

Sorry these formulas are above my knowledge. I appreciate the help.

Mike Wilday

No, you'd need that formula in each row. but to create a summary of each one you could add =sum(children()) into the parent row and it would sum all the children or dependent tasks of the parent.

See my screenshot for an example.

Sign in to join the conversation!

Quick Links

Trending discussions

How to Share Single Workspace Folder?
I have a workspace that I created with two subfolders. A user needs to have access to only one of them but when I have that folder selected, I can only share the entire workspace with them. Is there a secret way that I can share the parent folder and ensure that any new files created will be shared with the same people? I…
I have a Pro plan but I cannot create ANY reports
I have a Pro plan but I cannot create ANY reports. The system does not allow me to choose a folder, and consequently a sheet to create a row report and suggests an upgrade to Business or Enterprise. I'm quite new to Smartsheet and feel a bit disappointed with this limitation, or maybe I'm just doing something wrong. Alberto
Showing "stacked" data / Statuses of items by swimlane
I have multiple items that each have their own statuses and that roll up to "workstreams" or swimlanes. On the dashboard, I would like to show the progress of the items by swimlane without breaking it out into separate widgets for each. This would ideally be a stacked bar where each bar represented the Items and was broken…