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.
Answers
-
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!
-
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.
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!