Does smartsheet have the ability to generate a capacity report?

thadhymel

We need is the ability to have a form when we can enter a start date, end date and the number of hours to complete the task and then have smartsheet calculate the capacity for the employees during any given timeframe (week/month). For example, Person A may enter Task A that starts on Feb 1st and end on Feb 5th and will take 5 hr. So for that week, Person A has the capacity of 35 hours. If Person A enters tasks B on Feb 3rd with an end date of Feb 12th for 10 hours, then-Person A’s capacity for week 1 is now 31.25 hr. (40 hr – 8.75). Person A’s capacity for the combination of week 1 &2 is 65 hr (80 hr – 15 hr). Person A’s capacity for week 2 is 33.75 hr (40 hr – 6.25 hr). 

None of the templates provide seem to be able to work. I can generate the reports if the time entered begins and ends within a week(SUMIFS function) but not spanning multiple weeks for individuals or the team. Any assistance would be appreciated.

Bassam Khalil

Hi @thadhymel

Hope you are fine, you can solve this issue by adding helper columns to calculate the working day rate then you can create a formula using the SUMIFS function with @cell and MONTH function to calculate for each week and use + to repeat the formula to cover the task duration, if you like i can prepare it for you but i need you to supply a copy of your sheets in excel format (  (Delete/replace any confidential/sensitive information before sharing) .

please send it to my Email ( bassam.k@mobilproject.it )

thadhymel

Bassam - Thank you for the response my question. I'll send you the spreadsheets with a brief explanation. Thanks again.