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

Options
edited 12/09/19

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.

Tags:

• ✭✭✭✭✭✭
edited 04/27/18
Options

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.

• Options

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!

• ✭✭✭✭✭✭
Options

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

• Options

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.

• ✭✭✭✭✭✭
Options

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.