Planned vs actual progress (hope this can help you)
Hello everyone,
I'm a little bit new on smartsheet and the first thing that I faced was how to measure the variance of my project, I tried the forum, youtube and help from this page to get what I wanted, it was not what i expected. So to get that visibility I decided to ad a new column (% Planned) that shows the calculation based on the duration of each row and a RGYB (red,green,yellow,blue) column to have a visual indicator.
The formula that I used for the %planned was:
=ROUND(IF(TODAY() < Start1, 0, IF(TODAY() >= Finish1, 1, NETWORKDAYS(Start1, TODAY()) / NETWORKDAYS(Start1, Finish1))), 2)
and for the indicator was:
=IF(OR([% Complete]1 >= 1, IF(ISBLANK([% Complete]1), 0, [% Complete]1) > [%Planned]1), "Green", IF(AND([%Planned]1 = 0, (IF(ISBLANK([% Complete]1), 0, [% Complete]1) = 0)), "Blue", IF(IF(ISBLANK([% Complete]1), 0, [% Complete]1) >= ([%Planned]1 - 0.15), "Yellow", "Red")))
Comments
-
Nice idea,
if.... progress can be calculated in a linear way ;-)
Best regards
Stefan
Projektmanagement Professional in Frankfurt am Main, Germany
-
Excellent contribution.
I appreciate your time and share this valuable information.
Best Regards
Alvaro.
-
Hi Fabian,
I used your formula and updated the column name but the result says "#UNPARSEABLE.
Note: instead of start1 and finish1, I used Star Date and End date since these are the names of my columns.
Can you help me? See screenshot
Adelina
-
Hi Fabian,
Thanks for this contribution!
It really helped me in automating some tasks.
Shobhit
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives