Technical Question

Susan van Niekerk

Good Morning Brain Trust

I have a sheet and a report that gives tasks assigned to specific External Stakeholders

each task has a start date and an end date .......

We want to create a graph or report that will show us the count of the Externals but must break it down and report into the months ( between start and finish dates) - I cant add columns as some tasks will run into years

any thoughts

Paul Newcome

Do you mean you need to know how many are assigned for each month based on the start and end dates?

Paul Newcome

Do you mean you need to know how many are assigned for each month based on the start and end dates?

Susan van Niekerk

Hi Paul,

Yes, so if task 1 has a start date of 01/01/2024 and an end date of 01/01/2026 then we want it to break it down it months - counting that task in each of the months of 2024 and 2025 and 2026

The report report that we are sing is a dynamic scope report that pulls theses tasks from all our project plans, and we want to achieve is to see how many task are assigned to a specific external stakeholder and how many months are involved - ultimately producing something similar to this ( they are pulling all the data and dumping it into Excell to achieve this at the moment)

Paul Newcome

You would need a sheet that has 2 date type columns. The first date column would be the first of the month and the second date column would be the last day of the month. You would go month to month as you go down the rows Then you would have separate columns for each External. From there you would use COUNTIFS with cross sheet references to bring in the counts for each external based on the month and year of the date in the date type column.

=COUNTIFS({Task Sheet External Colum}, @cell = "Stakeholder 1", {Task Sheet Start Date Column}, @cell<= [Month End]@row, {Task Sheet End Date Column}, @cell>= [Month Start]@row)

Once you create this metrics sheet for each of the task sheets, you can create a report that pulls all of these metrics sheets together. You can have a separate text/number column that has labels that go along the lines of "01-2023", "02-2023", so on and so forth on each of the sheets and group by this column, apply a Sum summary in the report, and then use this to create your chart.

Susan van Niekerk

Hi Paul

We currently have 152 project plans( Control Centre Blue Print) - we have the start and end date columns - externals are in a drop down box, unfortunately we cannot add extra rows for a single task to split the dates, it would make the plan sheets way to cumbersome - each plan sheet can have up to 50 tasks for externals and running over 6 months to 5 years - so to create a set of rows of each task would just complicate for the teams

We may have to look at another software that could manipulate what we need

But thanks for your input

Paul Newcome

No. The extra rows for each month do not go on the task list. The rows for each month go on a separate sheet.