Smartsheet Basics

Smartsheet Basics

Ask questions about the core Smartsheet application: Sheets, Forms, Reports, Dashboards, and more.

Planned % Progress & Actual % Progress

✭✭✭✭

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

Answers

  • ✭✭✭✭✭

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

  • ✭✭✭✭

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

  • Community Champion

    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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • ✭✭✭✭✭

    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!

  • 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?

Trending in Smartsheet Basics