LEOPARD PROJECT CONTROLS

EXCEL FILE TO PRIMAVERA P6 TO UPDATE THE PROGRAM

The Baseline Programs are updated to monitor and track the progress of work every week or every month.

Generally, the baseline program is updated manually by entering the Actual Start, Actual Finish, and Performance % Complete for each activity and scheduling the program to a new data date. In General practice, the planning engineer visits the site and coordinates with the site team for progress updates. If the planner is unable to visit the project site, the updated program shall be prepared remotely by importing the Excel file with details of actual progress which shall be provided by the site team. To do this, the following procedures shall be followed.

An Excel template for progress updates shall be provided by the planning engineer, which shall be filled in by the site team. Once the above-mentioned Excel template is duly filled and received from the site team, the Actual Dates and Performance % Complete shall be imported to the Primavera P6 program from the Excel file. To do this we need to follow the steps below:

Step 1 :

We need to prepare a template in Excel with the following columns by copying the details from the baseline program and pasting them into a new Excel file:

  1. Activity ID
  2. Activity Name
  3. Original Duration
  4. Remaining Duration
  5. Planned Start (Baseline Early Start)
  6. Planned Finish (Baseline Early Finish)
  7. Actual Start
  8. Actual Finish
  9. Performance % Complete
Figure 1: Excel template for updating progress

Step 2 :

In Primavera P6, we copy the baseline program and rename it as an updated program and schedule it with the new Data Date (cut-off date for the updated program). In this program the Actual Start and Actual Finish dates are blank and the Performance % Complete is 0% for all the activities.

Figure 2: Schedule the Program The next step is to export the aforesaid program to Excel. To export the program to Excel, we click File, then Export, select “Spreadsheet-(XLS)” and click Next.
Figure 3: File Export
Figure 4: Exporting updated Program in Primavera P6 to Excel Once, we select the type of data to export and click “Next” the program navigates to the “Projects to Export” window.
Figure 5: Exporting Activities Data On the “Project to Export” window, we select our updated program and click on “Next” to move to the next window:
Figure 6: Selecting Projects to Export Now we need to select the activity data to be included when exporting to Microsoft Excel. This can be done on the “Select Template” window and by clicking on the “Add” button. Once we click the “Add” button “Modify Template” window will appear. On the Selected Options in “Modify Window”, we enter the “Template Name” as an updated program and select the following columns:
  • Activity ID
  • Activity Status
  • WBS Code
  • Activity Name
  • Original Duration
  • Remaining Duration
  • BL Project Start
  • BL Project Finish
  • Actual Start
  • Actual Finish
  • Performance % Complete
Figure 7: Modify Template Once the above-mentioned columns are included in the “Modify Template” window we will click the “OK” button, return to the “Select Template” window, and click Next.
Figure 8: Select Template In the next window under “Select Excel File,” we can name our “Excel File,” select the path for file location and click “Next” and the program navigates to the “Summary” window.
Figure 9: Select the XLS file In this window, the process of exporting will be finished once we click “Finish,” and we will have our Excel file exported to the location we saved it.
Figure 10: Excel Export – Summary

Step 3 :

Now we will open the aforesaid exported Excel file from Primavera P6 and insert a new worksheet. The progress data from the Excel file received from the site team (see Step 1) shall be copied into the new worksheet inserted in the exported Excel file from Primavera P6.

Figure 11: Copy Actual Progress to a new worksheet Now we will open the sheet named “TASK” (far left) in the exported Excel file from Primavera P6. In the aforesaid worksheet, the values for Actual Start, Actual Finish, and Performance % Complete (highlighted in the figure below) are to be transferred from the “new worksheet” by using the VLOOKUP formula. Once the new values for Actual Start and Actual Finish are transferred, we apply a filter in the top row of the “TASK” worksheet and delete values stored as “0” in the columns, Actual Start and Actual Finish. The values stored as numbers for Actual Start and Actual Finish shall be converted as dates by clicking the “Format” in the toolbar then “Format Cells,” “Number,” “Date” and selecting date format. Then we convert the values stored as text as numbers in the column named “Remaining Duration.” Once the values in “Remaining Duration” are converted as numbers, we assign the formula for all the cells for the “Remaining Duration” column as below: Remaining Duration = (Original Duration – (Original Duration x Performance % Complete)) When the above formula is applied the Remaining Duration for all the activities will be changed based on the Performance % Completed of the respective activity.
Figure 12: Amending values in the “TASK” worksheet

Step 4 :

Once the previous procedures are completed, the Excel sheet is to be closed before importing it to the Primavera P6. Then we open the updated program in Primavera P6 to import the mentioned, amended Excel file. To do that, we will click File, then “Import.” In “Import Window,” we will select “Spreadsheet-(XLS)” and click “Next.”

Figure 13: Import Excel file Then in “Excel Import Window,” we will select the Excel file exported previously from Primavera P6 which was amended with new values for Actual Start, Actual Finish, Performance % Complete, and Remaining Duration.
Figure 14: Select the Excel file to import Then if we click “Next,” the program navigates to the “Import Type” window wherein we will click all the checkboxes provided and click “Next.”

Figure 15: Excel Import- Import Type Then if we click “Next,” the program navigates to “Import Project Options” and we select the same program which is open for updating and click “Next.”

Figure 16: Excel Import – Import Project Options Once we select the Primavera 6 for “Import To” in the “Import Project Options” window and click “Next,” the program navigates to the “Summary” window. Then we will click finish in the “Summary” window.
Figure 17: Excel Import – Summary When we click “Finish” in the summary window, the import process starts and after a while, the window displays a text box as “ The import was successful.”
Figure 18: Excel Import – Summary- Successful Import Once we receive the message “ The import was successful,” we can see the new values for Actual Start, Actual Finish, Remaining Duration, and Performance % Complete are filled in the updated program as per the values in the “TASK” worksheet of Excel file used for Import.
Figure 19: Updated Primavera P6 Program with Actual Dates and Performance % Complete

Conclusion:

If the program is to be updated remotely by the planning engineer without visiting the project site, the aforesaid process shall be followed. This process of importing data for actual progress from an Excel file requires less time compared to manually entering the actual dates and Performance % Complete in the program.

Contact Leopard Project Controls today for your next CPM Scheduling Project.

Share This Blog, Choose Your Platform!

Go to Top