The Claim digger or Schedule Comparison is a widely used tool in P6 to compare between two projects and detect the differences.
One of the main shortage of the Claim Digger or the Schedule Comparison tool is detecting the changes in calendars.
The tool will not detect any revisions in calendars working hours or changing from Project to Global calendar if both have the same description.
Claim Digger will tell us nothing about calendars other than whether the name of the calendar is different.
In this article we will discuss how to detect the changes between calendars using excel.
We will need to use the XER File Parser tool or to connect an excel sheet with our SQL server or Oracle database.
I will add a link to download the XER Parser tool at the end of the article.
In order to detect the changes made to calendars we need to perform the following steps:
1 – Load the XER files in the XER Parser and take a copy of the CALENDAR and TASK tabs
· Open the XER Parser and load Project 1 XER file
· Copy the Calendar and Task tabs into a new workbook
· Repeat the same steps for Project no. 2 XER
2 – Create a new tab for the analysis and compare between the Calendars using excel formulas
· Create a new tab and rename it to Calendar analysis
· Copy the data in Column A to G from Calendar 1 and 2, and paste them in the new tab.
· Delete columns B, E and F then remove the duplicates from column A and B by going to the data tab then remove duplicates.
· Add 3 new columns (Calendar 1, Calendar 2, Changes) and insert the excel formulas to detect whether changes made or not to the calendars.
Insert the following formulas:
Cell E2: =INDEX(CALENDAR!A:M,MATCH('Calendar Analysis'!A2,CALENDAR!A:A,0),13)
Cell F2: =INDEX('CALENDAR (2)'!A:M,MATCH('Calendar Analysis'!A2,'CALENDAR (2)'!A:A,0),13)
Cell G2: =IF(E2=F2,"No","Yes")
Then fill down
· If the value in column G (Changes) is yes, then copy the row that has this value and paste values to remove the formulas from that row.
3 – Paste the data into 2 separate word documents and compare to know exactly the changes that have been done to the calendars
· Copy the data in column E in word file no. 1
· Copy the data in column F in word file no. 2
· Run a comparison report to detect the changes
Go to the review tab and click on the compare button in word
Choose the original and revised document (Document 1 & 2)
Then a list of changes will be detected, to understand the changes, any field looks like 0||1 or 0||2 , 0||3 ..etc. represent the day of the week so 0||1 represent the first day of the week in your P6 calendar 0||6 represent day number 6 in your calendar. If you found after the day something like this (0||0(s|07:00|f|17:00)()))) that represent the working hours. So if you found (0||2()( (0||0(s|07:00|f|17:00)()))) that means in day number 2 the working hours from 7 am till 5 pm. If there is no working hours in a specific day you will find it followed directly by the next day with no working hours information for example: (0||1()()) (0||2()( that means day number 1 is non-working day. After the 7 days of the week comes the holidays and the exceptions and it will be something like this: (0||Exceptions()( (0||0(d|42628) that means day number 42628 is an holiday to know the exact date just type this number in excel and format the cell as date and it will show you the exact date.
That being said and if you understood what it means, let’s return back to our comparison results. We basically have two changes, no. (1), day number 6 has been changed from non-working to working day. Change no.2 day number 42639 became non-working day.
So let’s check:
Day number 6 which is Friday became working day. And day number 42639 which September 26 became a non-working day.
Now we are able to detect exactly all the changes occurred in the calendar and that is the end of the first section of the article.
The second section of the article will discuss how to determine if the calendar of the activities changed from Project to Global if both calendars have the same description.
1 – Return back to the excel sheet and create a new tab and call it task analysis
2 – From the Task tabs copy columns B, D, N and O to the new task analysis tab and remove duplicates from columns C &D
3 – Add 5 new columns (Project 1 Cal, Project 1 Cal description, Project 2 Cal, Project 2 Cal description, Changes) and insert the formulas to detect any changes
Insert the following formulas:
Cell E2: =INDEX(TASK!D:O,MATCH('Task analysis'!C2,TASK!N:N,0),1)
Cell F2: =INDEX('Calendar Analysis'!A:B,MATCH('Task analysis'!E2,'Calendar Analysis'!A:A,0),2)
Cell G2: =INDEX('TASK (2)'!D:O,MATCH('Task analysis'!C2,'TASK (2)'!N:N,0),1)
Cell H2: =INDEX('Calendar Analysis'!A:B,MATCH('Task analysis'!G2,'Calendar Analysis'!A:A,0),2)
Cell I2: =IF(AND(F2=H2,E2<>G2),"Changed from Project to global",IF(AND(E2<>G2,F2<>H2),"Calendar Changed","No Changes"))
Now fill down the formula, and if the value in Column I is Changed from Project to Global, that means that this task calendar has been changed from Project calendar to global calendar.
Now we determined all the changes occurred to the calendars, if you don’t want to use XER Parser, you can connect excel directly to your database and recall the Calendar table, it will give you the same result.
The link for the XER File Parser Builder:
https://www.dropbox.com/s/w468q7um4zo955f/xerFileParserBuilder_2007.xlsm... [1]
Thank you and all the comments and suggestions are welcomed.