Resource Planning: Calculating Weekly Hours Remaining for a Project

Iftearn

Hello,

Goal: To view estimated hours per resource per week.

I am looking for a way to calculate the weekly hours remaining on a project, per person. I am referencing my project plan as the source sheet which has: Resource Name, Estimated Hours, Start Date, Due Date, Week Start, Task Name, etc.

In a summary sheet, I would like to see each team members name, the name of the project, their total hours for the project, and the hours needed for each week.

Ideally, I would like to have information pulling/calculating from multiple project plans into this summary sheet so that we can view each resources weekly capacity.

I have had no luck finding a formula to accurately calculate this so far.

Thank you,

Georgie

Hi @Iftearn,

Have you looked into row reports as a way of filtering information? You’d be able to add multiple project plan sheets as the source for a report, and you can then filter the report to show rows for certain resources, or group the report by resource. Take a look at the following help articles for more information:

• Build a row report
• Create filter criteria in Report Builder
• Group data to organize results in report builder

I also found some related threads here in the Community which may provide some useful suggestions for you - check them out:

• Calculating Monthly Labor Hours From Project Sheet
• I need a formula to calculate projected hours based on start/finish dates
• Calculating work completed and forecasts
• I need a formula to calculate projected hours based on start/finish dates

If this doesn’t help you reach your desired outcome, please provide some screenshots showing an example project plan sheet, and a summary sheet (with information manually entered so that we can understand exactly what you’re looking to see), and then hopefully other members can help with some more suggestions!

Hope that helps!

Georgie

LijuGeorge

Hello @Iftearn,
So for each resource across multiple projects: Total estimated hours, Weekly breakdown of hours needed, Pulled from project plans that contain Resource Name, Estimated Hours, Start Date, Due Date, etc.

Each task in your project plan must have the following columns: Make sure each row represents a task, and Estimated Hours total hours across the task duration.

Task Name Resource Name Estimated Hours Start Date Due Date Project Name

Now will create a “Weekly Resource Breakdown” Sheet (Summary Sheet)
Add a Helper Column in the Project Plan, Create a helper column named Week Identifier or Week Start using a formula that returns the start of the week
=DATE(YEAR([Start Date]@row ), MONTH([Start Date]@row ), DAY([Start Date]@row ) - WEEKDAY([Start Date]@row , 2) + 1)
you align all tasks to a weekly calendar (starting Monday).

Calculate Weekly Distribution
Since Smartsheet doesn't support native "split hours by week across duration," use the level-loading assumption:
Distribute Estimated Hour evenly over the task duration.
For each week in the task range, assign a fraction of hours based on number of working days that fall in that week.
This logic is best handled using a helper sheet or an automation tool, but here's a simple version in Smartsheet using manual steps or DataMesh/Bridge:

In your Summary Sheet, use SUMIFS across all weekly task breakdown rows to show total hours:=SUMIFS({Weekly Hours}, {Resource}, [Resource Name]@row , {Week Start}, [Week Start]@row )

Repeat this per resource, per project.

• Smartsheet Control Center or Bridge can automate the expansion and aggregation

• Consider using Reports for a more dynamic, real-time summary view

Regards
Liju George