Manpower Projection Formula

Options

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)

I really appreciate any help!

Answers

  • Deepanshu
    Options
    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
    Options

    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
    Jgorsich ✭✭✭
    Options

    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)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!