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

Carm T
Carm T
edited 01/06/22 in Formulas and Functions

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

  • markkrebs
    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!

  • 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
    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!