Count Projects With Start and End Dates That Overlap A Specific Range

gunnell

I have individual sheets for each project my team is working on. Sheets include Start and End dates for the project and this is added to the sheet summary. I'd like to count the number of projects that were "in development" each quarter and display that data in a bar chart.

For example:

Quarter ranges are:

Q1: 1/1 - 3/31

Q2: 4/1 - 6/30

Q3: 7/1 - 9/30

Q4: 10/2 - 12/31

Project 1 started on 7/9/22 and ended on 3/12/22. Therefore, it was "in development" during 2022Q3, 2022Q4, and 2023Q1. I would want this project to be counted in all three quarters and therefore represented 3x in a bar chart that displays a count of projects in development for each quarter.

I'm assuming I need to create a metric sheet with the following columns:

Title of quarter (e.g., "2022Q1") | Start Date (e.g., 1/1/22) | End Date (e.g., 3/31/22).

And then a 4th column with a formula that searches all sheets within the workspace and counts the number of projects whose start and end dates overlap with the start and end dates in the columns mentioned above.

I would need this to automatically update anytime a new sheet is created (based on the project template) for this workspace.

Thanks in advance.

Ryan Sides

Hye gunnell, you can create the metric sheet you mentioned, but you would need to point it to each project sheet (depending on how many you have, that might take awhile)

An alternative approach would be to create 4 new columns (or 4 new Sheet summary fields) that determine if the project was within each of the quarters on each project sheet.

Then use a report to pull those fields in and Group the 4 columns by "Count" in the report.

Then display that on your dashboard chart.

Does that make sense? Either way, you're putting in time creating cross sheet references or the 4 columns and their formulas.

P.S. As a shortcut, you can add the 4 columns to a blank sheet and "copy" the row to each of your projects. It will then build the 4 columns for you. You'll still need to go in and add the formulas though.

gunnell

Thanks for the response, Ryan. I should have clarified that I have projects that go back 10 years that I'd ideally be adding into smartsheet for historical data and we will continue working on new projects for the foreseeable future. So I would need to identify specific years as well as which quarter (2017Q1, 2022Q4, etc...). If I understand your proposed solution correctly, I would need more than just four columns or summary fields, I would need one for every quarter in every year in the past and more going forward.

Alternatively, is there a way that I could simply use a dropdown multi-select cell within a project sheet? I don't believe you can use multiselct dropdowns in summary fields, but if I have a cell within each project sheet that identifies the quarter(s) for that project, could I pull that information in somewhere else? I would need a formula that checks a specific cell in all sheets within a workspace and counts the quarters. I'm thinking a metric sheet with all of the quarters for every year across the columns, a new row for each project (ideally automatically generated based on sheets in the workspace), and a "1" in each cell with a corresponding quarter for that project. I could then simply sum up the 1s for each quarter and chart it out that way.

It seems the Reports features ALMOST has this functionality. It is able to summarize data across all sheets in the workspace automatically (without having to specifically tell the report there is a new sheet) but I can't see any way within a report to summarize multiselect dropdowns...

Ryan Sides

@gunnell

Do you have access to the premium app called control center?

This would be a perfect use case for that.

With control center you would have a portfolio sheet that would auto update for each of your projects. Your multi selects would then get counted by a metric sheet for each quarter of each year you are looking to report on.

Otherwise, you’ll need to connect your metric sheet manually to each of your multi select columns in each of your project sheets.

gunnell

I'm currently on a free trial. If that app isn't included in the Pro plan, then it probably wouldn't be doable for my office budget if we do decide to move to smartsheet.