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.
Comments

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.

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!

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)),"")

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.

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.
Categories
 All Categories
 14 Welcome to the Community
 10.5K Get Help
 61 Global Discussions
 46 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 54 Brandfolder
 124 Just for fun
 50 Community Job Board
 466 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives