How do I write a formula that adds "+" if the sum of two columns is more than 1

ASHH722

I have an anticipated finish date column and an actual finish date column. I have a formula to subtract the actual finish from the anticipated so that I can determine the variance. If for example the actual finish was 6 days longer than the anticipated, I want it to read "+6" and not just "6" (how it shows now). How do I add the "+" into the formula? Is there a completely different way to go about this as well instead of a simple SUM formula?

Mary_A

You use a text column --- then just add the result to a plus character like this:

=("+" + " " + NETDAYS([Date 1]@row, [Date 2]@row))

Looks like this in a sheet:

results in

You should note, your use of the plus sign is assuming the variance is always positive. With this formula, you may get an odd result if the finish date is before the anticipated end:

So you are better off using an if statement, so that the plus appears only with a positive netdays

What if projected and actual are on the same day? You'll a nested if to test for 1 which is what is returned in this case. All together, you get this as a solution:

=IF((NETDAYS([Date 1]@row, [Date 2]@row)) = 1, 0, IF((NETDAYS([Date 1]@row, [Date 2]@row)) > 0, ("+" + " " + NETDAYS([Date 1]@row, [Date 2]@row)), NETDAYS([Date 1]@row, [Date 2]@row)))

Gives you 0 if the dates are the same, plus if a positive value, and negative otherwise.

You actually might be better off not adding a plus sign altogether. As the function adds a negative when it is appropriate and without the negative, a positive value is generally assumed.