How do I create manpower chart based on a schedule?

rbryant

I am trying to create a manpower (resource) chart based on a construction schedule.

Paul Newcome

My suggestion would be to start with a separate sheet. We would put in two Sheet Summary fields to start. Both of them will be date fields. One will pull in the earliest date from the Project Plan and the other would pull in the last date.

Start Date:

=MIN(COLLECT({PP Start Date Column}, {PP Start Date Column}, @cell <> ""))

End Date:

=MAX(COLLECT({PP Start Date Column}, {PP Start Date Column}, @cell <> ""))

Then we would create a text/number column in this second sheet (called "Number" in this example) with the numbers starting at zero on row one and increasing as you go down the column until you have as many weeks as you think you will need (plus a buffer).

0

1

2

3

4

5

etc.

Next we need a date type column (called "Week Start" in this example) using the following column formula:

=IF((Date@row + (1 - WEEKDAY([Start Date]#))) + (7 * Number@row) <= [End Date]#, (Date@row + (1 - WEEKDAY([Start Date]#))) + (7 * Number@row))

Finally for the Manpower Needed column we use this:

=IF([Week Start]@row <> "", SUMIFS({PP Manpower Column}, {PP Start Date Column}, @cell<= [Week Start]@row + 6, {PP End Date Column}, @cell>= [Week Start]@row))

Now to create the chart we would first create a report that references this sheet we just built out and have it filtered to show only rows where [Week Start] is not blank. Include the appropriate columns and then use this to create the chart on the dashboard.

Paul Newcome

Would it be daily, weekly, monthly, or some other increment?

rbryant

Weekly.

Paul Newcome

My suggestion would be to start with a separate sheet. We would put in two Sheet Summary fields to start. Both of them will be date fields. One will pull in the earliest date from the Project Plan and the other would pull in the last date.

Start Date:

=MIN(COLLECT({PP Start Date Column}, {PP Start Date Column}, @cell <> ""))

End Date:

=MAX(COLLECT({PP Start Date Column}, {PP Start Date Column}, @cell <> ""))

Then we would create a text/number column in this second sheet (called "Number" in this example) with the numbers starting at zero on row one and increasing as you go down the column until you have as many weeks as you think you will need (plus a buffer).

0

1

2

3

4

5

etc.

Next we need a date type column (called "Week Start" in this example) using the following column formula:

=IF((Date@row + (1 - WEEKDAY([Start Date]#))) + (7 * Number@row) <= [End Date]#, (Date@row + (1 - WEEKDAY([Start Date]#))) + (7 * Number@row))

Finally for the Manpower Needed column we use this:

=IF([Week Start]@row <> "", SUMIFS({PP Manpower Column}, {PP Start Date Column}, @cell<= [Week Start]@row + 6, {PP End Date Column}, @cell>= [Week Start]@row))

Now to create the chart we would first create a report that references this sheet we just built out and have it filtered to show only rows where [Week Start] is not blank. Include the appropriate columns and then use this to create the chart on the dashboard.