We all export data from Primavera P6 to Microsoft Excel for analysis, progress updating & graphing. But sometimes getting clean data from P6 isn’t easy (even filtered data). Often we would like to manipulate the data in Excel (such as performing calculations in Excel using P6 numeric, date or cost data).
Unfortunately P6 formats all data exports as text (appends an apostrophe in front of all data). Using Excel “Format Cell” function will not change the text data to the data format you require but this tip will show you how to use another Excel function to change the data format in few steps. A sample P6 data export to Excel is shown below.
A close look at the “Original Duration” column shows that this has been exported as text instead of number. This is can confirmed by the apostrophe (‘) in front of the numbers.
Even the “Start” and “Finish” columns were exported as text not dates.
With the desired column selected, in this case Column E (Original Duration), click on the “Data” tab.
In the Data tab, look within the “Data Tools” group and click on “Text to Columns”
In the “Convert Text to Columns Wizard – Step 2 of 3” dialog, select “Tab” checkbox under Delimiter group. In the “Text qualifier” drop-down box, select apostrophe “’” and click on “Next”.
In the “Convert Text to Columns Wizard – Step 3 of 3” dialog, do not change any of the default setting but check to confirm the following and click “Finish”. • Under “Column data format” grouping, ensure that “General” is selected • In “Destination” textbox, ensure that the column shown is same as the one selected.
The data in Column E (Original Duration) is now converted to numbers with the apostrophes removed.
The “Start” and “Finish” columns data can also be changed from text to date data in the same way. To highlight this, let’s try to change the date format from “dd/mm/yyyy hh:mm” to dd-mmm-yy”. Select columns “F” and “G”, right-click and select “Format Cells”.
In the “Format Cells” dialog, select “Numbers” tab and under category, select “Custom”. Under “Type” list, select “dd-mmm-yy” and click “OK”.
You will notice there is no change in the date format and that is because they were exported as text fields.
But if we follow the “Convert Text to Columns” steps outlined earlier, columns F and G will be converted to the desired date formats as shown below.
I hope you find this tip useful. It has certainly helped me.