Construction Manpower Schedule

Sean Kelley

I have a construction schedule for installation which calculates the manpower/day required for each activity. I am trying expand the schedule dates to report manpower/day for each week and then graph that on a line chart. I can not figure out a formula to roll up the manpower for each week so I can then graph that information by each week of the project.

Devin Lee

Hey @Sean Kelley,

I would start by breaking down the man power for each day. Not sure exactly how your team is structured but it's likely you don't have the same man power per day throughout the entire week. Some days you will have more man power while others you might have less. Knowing this means you want to average the man power needed per week and round up so we don't cut anyone in half.

Make another sheet that lists all the dates for the entire duration of the project. Add a column called "Day Man Power" and insert the formula

=SUMIFS({Reference 1},{Reference 2}, <=Date@row, {Reference 3}, >=Date@row)

With the References being towards your original sheet. If they were on the same sheet it would look like this

=SUMIFS([Man Power]:[Man Power], [Start Date]:[Start Date], <=Date@row, [End Date]:[End Date], >=Date@row)

Then add two more columns for "Week" and for "Week Man Power"

In those rows put your week start dates and add the formula below to the Week Man Power in the top cell.

=ROUNDUP(AVERAGEIF(Date:Date, AND(Week@row <= @cell, Week2 >= @cell), [Day Man Power]:[Day Man Power]), 0)

I added in an image for reference. The two sheets are divided by the yellow line.

Devin Lee

Hey @Sean Kelley,

I would start by breaking down the man power for each day. Not sure exactly how your team is structured but it's likely you don't have the same man power per day throughout the entire week. Some days you will have more man power while others you might have less. Knowing this means you want to average the man power needed per week and round up so we don't cut anyone in half.

Make another sheet that lists all the dates for the entire duration of the project. Add a column called "Day Man Power" and insert the formula

=SUMIFS({Reference 1},{Reference 2}, <=Date@row, {Reference 3}, >=Date@row)

With the References being towards your original sheet. If they were on the same sheet it would look like this

=SUMIFS([Man Power]:[Man Power], [Start Date]:[Start Date], <=Date@row, [End Date]:[End Date], >=Date@row)

Then add two more columns for "Week" and for "Week Man Power"

In those rows put your week start dates and add the formula below to the Week Man Power in the top cell.

=ROUNDUP(AVERAGEIF(Date:Date, AND(Week@row <= @cell, Week2 >= @cell), [Day Man Power]:[Day Man Power]), 0)

I added in an image for reference. The two sheets are divided by the yellow line.

Sean Kelley

@Devin Lee Thanks for your help...