Manpower Projection Formula

David Bramer

I have multiple schedules for different projects with the same base information:

Task Name (Primary)

Start Work Date

End Work Date

Duration (Dependencies are enabled)

Manpower Needed

Many tasks have durations that overlap with other tasks.

I want a separate manpower projection sheet that tells me each day what the total manpower would be needed so I can forecast months/years in advance how many people I would need to do my jobs.

Example:

5/1 - 5/3: Total Manpower Needed = 10

5/2 - 5/4: Total Manpower Needed = 6

5/1 Total Manpower Needed = 10

5/2 Total Manpower Needed = 16

5/3 Total Manpower Needed = 16

5/4 Total Manpower Needed = 6

I have been trying the =SUMIFS formula but I can't figure out how to calculate the manpower needs between the SWD (Start Work Date) & EWD (End Work Date)

Current Formula: =SUMIFS({CMH076 - Active Schedule Estimated Manpower}, {CMH076 - Active Schedule SWD}, SWD@row)

2024-05-03 02_09_24-Window.png

2024-05-03 02_06_29-Window.png

I really appreciate any help!

Deepanshu

1. You can calculate the duration between the SWD and EWD.
2. In order to calculate the Manpower, you can find the average time one worker takes to complete the task in hours.

Formula: CEILING(Duration/ (Hours_per_worker *24), 1)

I am anticipating, if this would work.

Thanks,

Deepanshu Sharma

David Bramer

Sharma,

Thank you for the quick response. This formula did not work for me though.
Hours worked or to complete a task does not matter in this application. I strictly just need to know on any given day throughout the year how much manpower I need.

Jgorsich

don't forget that with sumifs you can have multiple criteria:

for manpower on a specific date try something like this: =SUMIFS({manpower},{startDate},dateYouCareAbout@row,{endDate},dateYouCareAbout@row)

For manpower between two different dates, this:

=SUMIFS({manpower},{startDate},">=" + startDateYouCareAbout@row,{endDate},"< =" + endDateYouCareAbout@row)

David Bramer

Jgorsich,

Using this formula:

=SUMIFS({Labor Schedule Range 1}, {Labor Schedule Range 2,">=" + SWD@row, {Labor Schedule Range 3},"<=" + EWD@row))

I am getting an unparseable.

I would like to clarify what I am trying to do.

I have multiple tasks with various durations. At some point, most tasks duration overlaps with another. I need to project out on any given day what my total labor needs would be for that day.

In the snip-it below I have manually inputted the numbers to show what they should be for each day due to the overlap in tasks.

Below that I had copied out the dates again and showed the formula I am using.