Schedule tracking - SPI vs user perspective

edited 12/09/19 in Smartsheet Basics


The scheduling tracking feature in Smartsheet shows that Parent Task is ahead of schedule for below, with Today = 10-Sep-2018

Task          From                 To                         Completion

Parent       06-Aug-2018     21-Sep-2018         83%

  Task A     06-Aug-2018    10-Aug-2018         100%

  Task B    13-Aug-2018     17-Aug-2018         100%

  Task C    20-Aug-2018     24-Aug-2018         100%

  Task D    27-Aug-2018     31-Aug-2018         100%

  Task E    03-Sep-2018     07-Sep-2018         100%

  Task F    17-Sep-2018     21-Sep-2018          0%

Completion percentage from Task A to Task F are input by user while Completion percentage of Parent is auto-calculated. And, Task F is scheduled in next week.

From my user's perspective, the project is ON SCHEDULE while the tracking of bar chart in gantt is AHEAD SCHEDULE.  See attached screen shot.

I think this is because the definition is different from each other.


In order to track the schedule from user's perspective, I have created the following two fields:

-  Expected completed percentage; and

-  SPI

with calculations below:

Expected completion Percentage

=IF([Planned Start]> TODAY(), 0, IF([Planned End] < TODAY(), 1, NETWORKDAYS([Planned Start], TODAY()) / NETWORKDAYS([Planned Start], [Planned End])))


=IF([Expected Completion (%)] <> 0, [Actual Completion (%)] / [Expected Completion (%)])

Eventually, the SPI calculated is exactly the same as what Smartsheet shows on gantt.



How to set the calculation if I want the result same as my user's expectation?


Thanks and regards





  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    The completion bar in the Gantt chart is not based on either SPI. It is sort of showing EV, but not quite. 

    In your example, all six tasks are 5d long (Based on the calcs show, I am assuming the Allocation % = 100%). That's 30d.

    25d are completed. So EV = 25/30 = 83%

    If you finished those on day 1, day 25, or day 30, the EV will still be 83%

    Your [Expected Completed Percentage] is incorrect because your formula does not account for the fact that the 30d are completed in a 40d time frame.

    The customer is correct, the project is on schedule. SPI should be 1.0

    Also, the calc used for the [% Complete] seen in the Gantt chart is not configurable.



  • FrankW

    Hello Craig,

    Thank you for your advice.

    Indeed, my original calculation was incorrect.




  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    You are welcome.