How to show scope versus capacity per month using date ranges?

Carm T

Hello! I have a dashboard that is currently showing values like Estimate in Days versus Allocation in Days to show the scope of the work versus the actual days a person is assigned to the work (i.e. Scope in Days versus Actual Allocation in Days).

I have this working to show scope versus capacity per fiscal quarter but now I need to show it per MONTH. I need to see if we are staffed appropriately each month.

Here's a screenshot where I show who is assigned to which feature and I use a date range to show how long they are assigned to that feature.

markkrebs

Is this the only sheet or are there multiples and do you own Pivot?

Options:

1) Add a helper column with a column formula =Year(date column)+" - "+Month(date column) then create a report grouped by the new column. this will give you a summary vertically.

2) if you want a horizontal look use pivot and have the new column be the columns in the pivot and then sum/count any values you need in the values section.

hope this helps!

Carm T

Hello! Thanks for responding!

Unfortunately, I do not have access to the Pivot app.

Regarding a non-Pivot solution: The trickiness lies in using this Date Range to indicate the days/months that a task/feature will take. So in the screenshot I have above, I am showing that this particular feature will take Jason two months (all of February and all of March). I'm trying to find a way to show Jason's capacity per month by utilizing this date range - I believe that I can do it if I can find a way for Smartsheet to recognize the days/months in the date range.

markkrebs

Ahh... yes you can do this with a few helper columns: Note: These formulas are directional and not usable with a cut and paste.

1) days in the first month: something like: NETWORKDAYS([Project Start Date]@row, DATE(YEAR([Project Start Date]@row), MONTH([Project Start Date]@row), VALUE([Last Day of Start]@row)), {Holidays Range 1})) where [Last Day of Start]@row is a column with a formula that calcs the last day of the month.

2) days in the last month: -1 * (NETWORKDAYS([Project Completion Date]@row, DATE(YEAR([Project Completion Date]@row), MONTH([Project Completion Date]@row), 1), {Holidays Range 1})

3) for the full months in between you can use a standard number of working days or reference a lookup sheet.

then (i know this is exhausting) you can add 12 columns per year and then use a long if statement to count the days in the month for the task using 1-3. I'd setup maybe 2 to 3 years so you dont need to touch the formulas for a while.

Let me know and we can hop on a call to figure it out if needed. Resource management (formerly 10000 ft) does this stuff automatically but its ~10-15K / yr I think.