Variance formula

Yaya

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

Much appreciate your help!

Thanks

Yaya

Paul Newcome

Try something like this:

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

Ray Lindstrom

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

Yaya

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

Ray Lindstrom

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

Yaya

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?

Yaya

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

Paul Newcome

Try something like this:

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

Yaya

amazing. thanks @paul!

Paul Newcome

Happy to help. 👍️

MazU

@Paul Newcome Hi Paul, how do I use the formula you suggested to exclude weekends?

Paul Newcome

@MazU The NETWORKDAYS does usually exclude weekends automatically.