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

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.

Find more posts tagged with

Accepted answers

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.