# Variance formula

Options
✭✭
edited 10/18/22

Hi,

I'd like to create a formula for caculating variance between baseline date and finish date. The baseline function doesn't really work well in SS.

The logic is if there are no dates in the Baseline finish column, then keep the variance cell empty.

If there are dates in the Baseline column, then calculate the networking days between Baseline finish date and actual finish date. If actual finish date is delayed than the baseline finish date, then I'd like it to show Minus -.

Thanks

Yaya

• ✭✭✭✭✭✭
Options

Try something like this:

=IFERROR(NETWORKDAYS([Actual Finish Date]@row, [Baseline Finish Date]@row), "")

• ✭✭✭✭✭✭
Options

Hi @Yaya,

I use an Target Duration as well as an Actual Duration. I then use the following column formula for a column I call "Duration Variance" to show how long something was scheduled to take, and how we performed against that. It shows whether we were x days less that target, or x days more than target.

=IFERROR(IF([Target Duration]@row > [Actual Duration]@row, ABS([Target Duration]@row - [Actual Duration]@row) + " days < Target", IF([Target Duration]@row < [Actual Duration]@row, ABS([Target Duration]@row - [Actual Duration]@row) + " days > Target", IF([Target Duration]@row = [Actual Duration]@row, "No Variance", ""))), "")

Hope this is what you were meaning, and that this was helpful.

BRgds,

-Ray

• ✭✭
Options

Hi Ray,

I am more referring to the INVALID data result, as some rows do not have baseline finish dates.

The logic I am thinking is: for rows, there isn't a baseline finish date? variance cell is empty.

If there is baseline finish date, then calculate the number of networking days (baseline finish - finish).

• ✭✭✭✭✭✭
edited 10/18/22
Options

Hi @Yaya,

I believe you're on the right track. It does seem like that invalid error is because of the null value That's why I start my formula with the IFERROR. That way when a cell is blank, it doesn't cause an invalid.

The final "") in my formula is what is displayed if there is an error. Basically an empty string.

BRgds,

-Ray

• ✭✭
Options

Thanks Ray, I don't have a baseline duration. Any chance to create a formula based on baseline finish date and actual finish date? With consideration to the empty baseline finish date?

• ✭✭
Options

@Genevieve P. is this something you can help with? Thanks!

• ✭✭✭✭✭✭
Options

Try something like this:

=IFERROR(NETWORKDAYS([Actual Finish Date]@row, [Baseline Finish Date]@row), "")

• ✭✭
Options

amazing. thanks @paul!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!