Report and Dashboard View of Planned vs Actual progress Task

ilene_healy68056

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

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