Report and Dashboard View of Planned vs Actual progress Task

I have searched the community for some solutions for what I am trying to achieve…and there were lots of information that seemed close to what I am trying to do. Many links that were shared for other blogs, articles, etc., are no longer active. So I am hoping someone from the community can help me put it together.

We would like to create a simple view of the timeline in bi-weekly buckets for the rest of this year and into next year that includes the count of tasks to be completed with the planned vs actuals dates, percent of the project complete. I’d like to turn this into a graph, which I saw some good examples of in the community, but not quite sure how to put it together.

To get started, I have created some helper columns

Copied and locked the Start and End dates as baselines for reference. These baseline start and end dates I think should be used for the comparison to Actual dates.
Added Actual Start and End Date columns
Added 2 columns to calculate the variance between my planned start date & actual start date and planned end date & actual end date.

I think the best approach is to set up a separate sheet with the biweekly reporting dates in a column and then count the tasks from my main project sheet for the baseline dates and actual dates to use for the graphing, but wanted to check with the community for any experience in this type of reporting and what are some of the best practices.

Thanks!

Ilene

2019-08-13 18_43_51-SP Automation Detail - Internal Sites NA TC1 - Smartsheet.com_.png

Find more posts tagged with

Formulas

Reports

Comments

Alejandra

Hi Ilene,

If you're looking to graph the number of completed tasks, you could create a metrics table using the COUNTIF or COUNTIFS function (depending on the number of criteria).

To count the number of tasks based on the baseline or actual dates, you could create a week number column for your planned and actual end dates and then use the COUNTIFS function to count completed tasks by week numbers.

ilene_healy68056

Thank you for your response. I did start down this path and have posted a new question where using week number created a snag where there was some overlap between week number and year. I am trying to report 2019, but 2020 dates are being pulled in...or the 2020 week 1 is counted as 2019 based on date. I just posted this to the community for help. I think I'm close though.

thanks!

Ilene

Sign in to join the conversation!

Quick Links

Trending discussions

How to split contents from a cell from a smartsheet into another smartsheet
I need to extract and split the contents of a cell in a Smartsheet into three separate columns to a new smartsheet. The cell entries are originally submitted through a form.
Information in a form populates specific cell.
Hello, Just investigating an idea. I'm building a staff register sheet. I'm curious to know if its possible to populate specific cells by the answers given in a form? So if, in a form, a staff member gives their name, the date and that they will be on leave, sick, training ect. Once the form is submitted, it will populate…
A multiple SUMIF function
My current formula is below. I would like to update this so that I only get a total of those that have been Quoted and have a timeframe data of 2025. How do I add this extra filter into the formula? =SUMIF(Status:Status, "Quoted", [Est. GP]:[Est. GP])