P6 Date Formats – Quick Data Hacks!

In dealing with P6 data, sometimes what you expect, is not what you get. When it comes to date formats, this is quite relevant. Here is a guide to transform XER and P6 copy-pasted values into proper date formats.

Problems with XER file format

When dealing with a native XER file, you need to be careful because you can’t always use the field you want. Below is a screen print from a typical TASK dataset.

When an actual start/finish date has been captured (as seen in the “act_start_date” and “act_end_date” fields above), then the dates that are stored in the “early_start_date” and “early_end_date” fields are no longer valid.

Thus, you need a routine to check if an actual start date exists, and use that in lieu of the early dates. Ultimately there is no good way to deal with, excel to write a routine somewhere in your data import routines – if importing in a database. Another option is to edit your XER import excel file to add 2 new columns for simply “start” and “finish” that will run your check for you in the native excel file before you import the data into a database.  This is an easy hack anywhere you manage your data: However, a core issue here is:

Where will you clean your data?

For me excel is often easier, but that does mean your processes will not be fully automated. Either way, the fix is fundamentally as seen below

 START=IF(ISBLANK(act_start_date),early_start_date,act_start_date)

FINISH=IF(ISBLANK(act_end_date),early_end_date,act_end_date)

 

Problems with P6 format (copy-paste)

If you are not dealing with an XER, you will likely simply copy-paste directly from P6 into Excel. Here again, we have to deal with a few (minor annoying) complications. Everyone I know in the planning world deals with this and has their own routines. I wish I could post them all, because some truly elegant solutions exist. The below is not meant to be “do it this way”, just more of an indication that if perhaps you are running into difficulty “this will work!”.

Start	Finish
22-Oct-18 A	9-Sep-19
	09-Sep-19*
	09-Sep-19*
10-Sep-19	10-Sep-19
05-Jul-19*	10-Sep-19
27-May-19 A	10-Sep-19
03-Dec-18 A	10-Sep-19

Above we can see a typical copy-paste result from P6. Obviously, this is filled with non-date formatted cells. This is caused by the ” A” indicator for activities that have an actual date, and a “*” for activities that have some sort of constraint applied. I have seen a few equation based solutions to strip the bad characters out. I find a code based solution to be slightly more elegant. It also means I do not have to deal with adding extra columns to my file, or performing any copy-paste values. But again, I am sure we all have nice solutions.

The easy fix I use: Scrubb_Data().

Sub Scrubb_Data()

Dim xChars As String
xChars = “*”
‘
Sheets(“P6_CURRENT”).Select
ActiveWindow.SmallScroll ToRight:=3
Columns(“V:V”).Select
Selection.TextToColumns Destination:=Range(“V1”), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=True, OtherChar:=xChars, FieldInfo _
:=Array(Array(1, 3), Array(2, 9), Array(3, 9)), TrailingMinusNumbers:=True

End Sub

This routine only works on 1 column at a time. For me, I can simply copy and paste the select statement and insert a routine for each excel column with a P6 date  (typically just start and finish). After pasting in P6 data, I will open the routine and click “play”. You will need a macro enabled file and will need to be at least a little comfortable with “view code”. There are a few options to auto call this function using a button, call the function when you close the file, many different options.. They all require a bit of VBA knowledge, although, like seriously, who isn’t in our world.

For the above, I ripped most of this from a routine someone else wrote (a routine that stripped the ” A” off the string. I had to add the xChars = “*” aspect.

For a find/replace statement, it is possible to use the below string to strip the “*” off. Here we have to use ~*~ because if we use *, it will replace the entire string. Again, a million ways to handle this.

Conclusion

Again, there are a 100 ways to skin this cat, and all achieve the same result. When we deal with data from P6, I find it so amazing that when you ask 10 people how we deal with the data, you will end up with at least 20 replies. Ultimately,  I believe this issue is a telling critical flaw in the underlying software. In the digital world, we need a completely different paradigm shift in the way we store and manage data.

Specifically in the Project Management world, I doubt anyone who uses tools such as JIRA or DevOps have to deal with annoyances such as this. Thus, perhaps we too should be using those tools!