Variance formula
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
Best Answer
-
Try something like this:
=IFERROR(NETWORKDAYS([Actual Finish Date]@row, [Baseline Finish Date]@row), "")
Answers
-
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
-
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).
-
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
-
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?
-
@Genevieve P. is this something you can help with? Thanks!
-
Try something like this:
=IFERROR(NETWORKDAYS([Actual Finish Date]@row, [Baseline Finish Date]@row), "")
-
amazing. thanks @paul!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!