Displaying actual/planned progress in dashboard



I have a construction gantt programme in Smartsheet with start & end dates and % complete columns for each task plus a 'parent' row for overall progress.

Is it possible for me to take data from this programme to display with a simple bar chart or line graph our actual progress against planned progress ?

Not sure how I can do this has the only % number displayed is the 'actual' one which changes as individual task % are updated. I can't see how we would get the data for planned progress ?

Any help would be greatly appreciated.

Thank you



  • Amber Eakin
    Amber Eakin ✭✭✭✭✭✭

    Hi @Alan R - How do you determine the planned progress percentage? Is it that you hope to be at a certain % by a certain date? If so, you could set up a Helper column (which you could then hide if you don't want it showing on the sheet) using IF statements with dates. Below is an example. It's saying if the current date is greater than or after March 1, it should be 75% complete. If it's February 1 or later, 50%. If it's January 1 or later, 25%.

    =IF(TODAY() >= DATE(2023, 3, 1), 0.75, IF(TODAY() >= DATE(2023, 2, 1), 0.5, IF(TODAY() >= DATE(2023, 1, 1), 0.25)))

    Amber Eakin, MSLS, M.Ed.

    Adult Education Specialist | Process Improvement Enthusiast

  • Alan R
    Alan R ✭✭✭✭

    Thank you Amber. That does help by giving me an idea of how it should be done, but I think I'm going to just add in another row and call it planned progress. I can update the percentage manually on that one to bring us to the current date and take that percentage to the dashboard.

    Thanks again.