Hi all — hoping someone has solved this before I go and reinvent it badly. What I have One row per project activity in a sheet, with columns for: • Developer hours • Design hours • PM hours • Service type/Milestone • Start date • End date What I want A weekly resource forecast — week commencing across the columns, service type and resource type down the rows, and summed hours in the middle. Essentially a capacity view so I can see how loaded each role is week by week across my portfolio. Where I'm stuck Two things: * Resource type isn't a value in my data, it's implied by which of the two hours columns is populated. I assume I need to restructure to one row per activity per resource with a single Hours column — is that what everyone does, or is there a neater way? * The bigger one: hours need to be distributed across the weeks between start and end date. If I just derive a "week commencing" column from the start date, a 15-hour activity running three weeks lands as 15 hours in week one and nothing after, which is useless as a forecast. My understanding is that Pivot aggregates rows that already exist rather than apportioning across a range, so the expansion has to happen upstream. Is that right? What I'm after How have others handled the apportionment? Options I can see: • Generate one row per activity per resource per week via Data Shuttle or Bridge • Do the expansion in Excel/Power Query and import the result • Abandon the sheet approach and use Resource Management instead Even distribution across the range would be fine as a starting point — I don't need front-loading or custom curves yet. Grateful for any steers, including "you're going about this completely wrong."

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Smartsheet Community

Connect with peers, share your expertise, and inspire what’s next in Smartsheet — from proven practices to practical how-to insights from fellow users and product experts.

How do you spread activity hours across weeks for a resource forecast?

msmariaa

Hi all — hoping someone has solved this before I go and reinvent it badly.

What I have

One row per project activity in a sheet, with columns for:
• Developer hours
• Design hours
• PM hours
• Service type/Milestone
• Start date
• End date

What I want

A weekly resource forecast — week commencing across the columns, service type and resource type down the rows, and summed hours in the middle. Essentially a capacity view so I can see how loaded each role is week by week across my portfolio.

Where I'm stuck

Two things:

Resource type isn't a value in my data, it's implied by which of the two hours columns is populated. I assume I need to restructure to one row per activity per resource with a single Hours column — is that what everyone does, or is there a neater way?
The bigger one: hours need to be distributed across the weeks between start and end date. If I just derive a "week commencing" column from the start date, a 15-hour activity running three weeks lands as 15 hours in week one and nothing after, which is useless as a forecast.

My understanding is that Pivot aggregates rows that already exist rather than apportioning across a range, so the expansion has to happen upstream. Is that right?

What I'm after

How have others handled the apportionment? Options I can see:
• Generate one row per activity per resource per week via Data Shuttle or Bridge
• Do the expansion in Excel/Power Query and import the result
• Abandon the sheet approach and use Resource Management instead

Even distribution across the range would be fine as a starting point — I don't need front-loading or custom curves yet.

Grateful for any steers, including "you're going about this completely wrong."

Find more posts tagged with

Sheets

Formulas

Reports

functionality

Pivot App

Accepted answers

All comments

Paul Newcome

Are you able to provide some screenshots with mocked-up / manually entered data for context?

msmariaa

Hi @Paul Newcome
Thanks for picking this up. Screenshot attached; here's the source data written out plus the output I'm after.

Source sheet — one row per activity, resource type implied by which hours column is populated:

Desired output —
A grid where week commencing runs across the columns, and Service Type broken down by resource type runs down the rows. Each cell is the total hours that combination consumes in that week.

Two constraints I should have led with:

No premium add-ons — no Data Shuttle, no Bridge, no Resource Management. Core Smartsheet only.
It needs to recalculate automatically when dates shift, so a one-off import of expanded rows won't work.

Paul Newcome

Am I to assume that all 52/53 weeks of the year will have their own column and be in chronological order from left to right, or just weeks that have dates in them are displayed?

msmariaa

Hi @Paul Newcome
thanks for sticking with this.

Fixed columns, chronological left to right, is fine and honestly what I'd prefer. I'd rather the set of columns stayed static and the numbers moved than have columns appearing and disappearing as dates shift.

Two caveats on that:

• A single calendar year won't quite cover it — some of my plans run into next year, so realistically I need the grid to span roughly 18 months. I'm assuming that just means more columns carrying the same formula rather than anything structurally different.
• Empty weeks showing as blank or 0 is fine. If it gets unwieldy I can hide or filter columns; the grid doesn't need to self-trim.

On the row side, the combinations are known and finite (service type × resource type), so I'm happy for those to be fixed rows too rather than generated.

For context on where my head is at: I'd expect each activity row to carry a per-week apportionment with the summary grid then summing by combination. But if there's a cleaner route I'm all ears.

Paul Newcome

Ok. And how do we determine the starting week? Would we be able to use the earliest [Start Date] and then just add 7 days from there for each weekly column?

msmariaa

Hi @Paul Newcome
To be clear on both points:

Week commencing = the Sunday of each week. So each column covers Sunday through Saturday.
I don't need the starting week calculated. I'll write the dates in manually, working from the earliest [Start Date] in the sheet (i.e. the date the project starts) and adding 7 days per column from there.

The reason I'd rather type them than derive them: if the starting week is driven by a formula off the earliest [Start Date], the entire grid re-labels the moment someone pulls a date forward, and I'm back to columns shifting under me. Typing them in once fixes the grid, so only the numbers move when dates change — which is what I'm after.

So you can treat the week commencing dates as known, fixed values. The part I still need is the formula that apportions each activity's hours across those columns.

msmariaa

To be clear on both points:

Week commencing = the Sunday of each week. So each column covers Sunday through Saturday.
I don't need the starting week calculated. I'll write the dates in manually, working from the earliest [Start Date] in the sheet (i.e. the date the project starts) and adding 7 days per column from there.

So you can treat the week commencing dates as known, fixed values. The part I still need is the formula that apportions each activity's hours across those columns.

Paul Newcome

In that case, I would suggest setting up the sheet so that you can just enter the first date in a sheet summary field (called "Start Date" in this example) and then use formulas to populate the dates across the columns. This will save you some work for each setup.

Since you are manually entering the date, I assume you will select the appropriate Sunday.

In row 1, I would manually enter the week numbers (1, 2, 3, 4, so on and so forth).

Then in row 2 you can use this formula to output the appropriate dates:

=([Start Date]# + (([Column1]1 - 1) * 7)) + ""

Finally, the formulas to get your hours would be something along the lines of

=SUMIFS({PM Planned Hours}, {Service Type}, @cell = "Consulting Services", {Start Date}, @cell <= [Start Date]# + ([Column1]1 * 7), {End Date}, @cell >= [Start Date]# + (([Column1]1 - 1) * 7))

You would use the same syntax for the other formulas but change the PM Planned Hours to Designer Planned Hours and Service Type to Project Management as applicable for those top three calcs. Total hours would be adding cell references together along the lines of

=[Column1]3 + [Column1]5

Once you get those set in the first column, you can drag-fill them to the right for the rest of the columns.

msmariaa

Thank you sooo much for your guidance @Paul Newcome

Paul Newcome

@msmariaa Happy to help. 👍️

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, category/bannerUrl, category/bannerImageAlt, category/extensions, category/customizationLabels, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/bannerImageAlt, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.