Wednesday, 16 March 2016

Determine First Finish & Last Start Dates of activities within a WBS

Summarised or rolled-up schedules only show Start & Finish dates of a WBS (i.e. first start date & last finish date of activities within the WBS). So how do we present a summarised schedule which also shows the first finish date & last start date of activities within the WBS?

One way is to export your Primavera P6 or Microsoft Project schedule data to Excel and use Excel's rich functions to achieve the desired results. This tutorial assumes you know how to export schedule data to Excel and also know how to manipulate Excel formulas.

Assuming we have the exported P6 data shown below, we will generate the summary schedule report we want using the WBS Name, Start & Finish columns. (See my blog post on Plan Academy on how to clean up exported P6 data for manipulation)
But before we start, we need to delete all Start & Finish Milestones, so that there is no blank cell in our data.
Create a new tab in the spreadsheet and format the schedule data table to look like the image below. The P6 schedule used in this example has 27 WBS elements.
To get the dates we want, instead of using VLOOKUP or INDEX and MATCH functions, we will treat the exported data in the Task tab as a Data Array and use MAX and MIN functions.

Starting with cell C3, enter the following formula to get the first start date:

=MIN(IF(TASK!$D$3:$D$120=$B3,TASK!$F$3:$F$120))

Being an array,  we now need to press Ctrl + Shit + Enter keys at the same time to change the formula into an array (i.e. add { } to the formula)

{=MIN(IF(TASK!$D$3:$D$120=$B3,TASK!$F$3:$F$120))}

Result shows the first start date of activities under Brick WBS and where TASK!$D$3:$D$120 is the WBS Name column from the Task tab and TASK!$F$3:$F$120 is the Start Date column from the Task tab. (Note use of relative column referencing in $B3, this will enable us copy the formula for other WBS Names in column B)

Now copy cell C3 and paste (as formula) in cell E3 and then edit formula by changing MIN to MAX so that it looks like

{=MAX(IF(TASK!$D$3:$D$120=$B3,TASK!$F$3:$F$120))}
Result shows the last start date of activities under Brick WBS

Now for finish dates, copy cell C3 and paste in cell D3 and then edit formula by changing reference from Task tab column F to column G, so that formula looks like

 {=MIN(IF(TASK!$D$3:$D$120=$B3,TASK!$G$3:$G$120))}
Result shows the first start date of activities under Brick WBS and where TASK!$G$3:$G$120 is the Finish Date column from the Task tab.

Now copy cell D3 and paste (as formula) in cell F3 and then edit formula by changing MIN to MAX so that it looks like

 {=MAX(IF(TASK!$D$3:$D$120=$B3,TASK!$G$3:$G$120))}
Result shows the last finish date of activities under Brick WBS.

Now that all the dates for the first WBS elements have been determined, select & copy cells C3 to F3 and paste (as formula) across cells C4 to F29 and this will generate the dates for the other WBS elements as shown below.
And that is it, a simple summarised schedule data (usually 1 page) you can present to your project team.

TAKING IT FURTHER
  1. You can also generate a report for Activity Codes instead of WBS Names
  2. You can also generate a report combining WBS Names & Activity Codes
  3. You can also generate this report for a reporting period
  4. If using Microsoft Project exported data, you can generate a report using Summary Activity Title AND Outline Number or Outline Number AND Outline Level

 

1 comment: