Options
edited 12/09/19

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

• Options

Nice idea,

if.... progress can be calculated in a linear way ;-)

Best regards

Stefan

Projektmanagement Professional in Frankfurt am Main, Germany

• Options

Excellent contribution.

Best Regards

Alvaro.

• edited 02/14/19
Options

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

• Options

Hi Fabian,

Thanks for this contribution!

It really helped me in automating some tasks.

Shobhit