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

Bri Boyer

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.

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.