Planned % Progress & Actual % Progress

IPC

hi,

I am using the project plan template that has the Schedule delta columns that compare the Planned & Actual start & end dates. This also automatically triggers a RAG in the Schedule Health. This is clear.

However, I am looking at how to identify the "Planned % Complete" based on the TargetStart & Target End dates? Does the Smartsheet have a calculation of the % Planned complete based on the Target start & end?

Thanks in advance for any advice.

Irene

PWNA Sam Harwart

Maybe something like IF([Start Date]@row>TODAY(), TODAY()-[Start Date]@row] / [Duration], "0%")?

You'll want to first check and see if it should have started, and if so, use the difference between today's date and the start date compared to the total duration to calculate the planned %.

IPC

Hi @PWNA Sam Harwart ,

Thanks for your reply. It is actually resulting to all 0%. In terms of dividing over the duration, the duration column is in terms of days (i.e. 5D, 10D), and therefore doesn't calculate correctly. I used another numeric column just to test the equation (i.e. predecessor column which is purely numeric), result still the same. I guess it's the , "else, 0%" thing that makes it all 0%.

I did a little more googling and found this which worked, i wanna share as well.

=ROUND(IF(TODAY() < [Target Start Date]@row, 0, IF(TODAY() >= [Target End Date]@row, 1, NETWORKDAYS([Target Start Date]@row, TODAY()) / NETWORKDAYS([Target Start Date]@row, [Target End Date]@row))), 2)

(Credit to the owner of that equation which, i'm sorry, i didn't recall due to a lot of searching).

It works in the column "Planned %.

Andrée Starå

Hi @IPC

I hope you're well and safe!

Excellent! Glad you got it working!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Please support the Community by marking your post with the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

PWNA Sam Harwart

https://community.smartsheet.com/discussion/comment/346712#Comment_346712

Glad you got it figured out! I didn't test out the exact syntax I shared but hopefully it was helpful on your journey to the solution!

Chamudi Withanawasam

https://community.smartsheet.com/discussion/comment/346712#Comment_346712

Thanks. I used this formula in the actual % complete but the value is different to that calculated by smartsheet! Any idea how smartsheet calculate the % complete?