Show Calendar Days instead of dates in Primavera P6 Schedule columns
In this tutorial, I will show you 2 methods of displaying ordinal dates in a Primavera P6 schedule, the first method uses Microsoft Excel and the second uses Global Change facility in P6. Ordinal dates schedules are usually required when a project does not have a defined calendar start date.
This is the Primavera P6 version of the Microsoft Project tutorial about showing ordinal dates in the Gantt chart timescale & calendar days in the Start & Finish columns.
Apologies for the poor audio quality in the video
Common Steps
An important setting for this tutorial is the P6 calendar to use. My preference is to use an 8-hour day, 7-day week calendar for all activities. And the 8 hours should be spread over a 9-hour period, i.e. 08:00 to 17:00 with an hour's break between 12:00 to 13:00 as shown below.
Another important setting is enable 24 hour (13:30) time in the Dates tab of User Preference and then ensure that in the Dates tab of Details form in Projects Layout, the Project Planned Start & Data Date have 08:00 as the time part of their dates (should be same dates). Once this is confirmed, you can go back to User Preference and select the Do not show time radio button.
For both methods, we would need to set-up the ordinal dates timescale. To do that, right-click anywhere on Gantt chart timescale and select Timescale in the pop-up menu
In the Timescale dialog box, select the Show Ordinal Dates radio button and for Ordinal Start, select PS - Earliest Project Start and for Ordinal Date Interval, select Month (or your preferred interval) and then click OK.
The Gantt chart timescale should now display Month 1, 2, 3 etc
Save your layout at this point.
Method 1 - Using Excel
Create 2 User Defined Fields (UDFs) of type Integer and name them Start Day & Finish Day.
Now add the 2 newly created UDFs to the layout and then save the layout.
We now need to export to Excel (Spreadsheet) and the Export Type is Activities. Fields to add to a new Export Template are Activity ID, Activity Name, Start, Finish, Start Day & Finish Day, as shown in image below. Having a bespoke template means you can always reuse it in future.
Now open the newly exported Excel file and it should look like the screenshot below. I have highlighted the project start milestone (MN-1000 - Effective Date in this example) for emphasis.
Enter the following formula in cell G3,
=E3 - $E$3 + 1
where:
$E$3 is the cell with the project start milestone (your project start milestone might be in a different cell)
"+1" is so that our calendar day starts at Day 1 instead of Day 0
Now Fill Down the formula so that you spreadsheet looks like the image below. Ignore the #VALUE! errors that appear against Finish Milestones for now.
Now enter the following formula in cell H3 and then Fill Down and once again ignore the #VALUE! error that appears against Start Milestones.
= (F3 - $E$3 + 1) - 9/24
where
9/24 is subtracted so that we do not have decimal places in our calculations.
The 9 is from the 9-hour period of our calendar, i.e. from 08:00 to 17:00
The 24 is from 24 hours that makes a day in Excel
The #VALUE! errors occur because in Primavera P6, Start Milestones do not have finish dates and similarly, Finish Milestones do not have start dates. But for our Calendar Days, we want milestones to have Start Day & Finish Day numbers. Therefore in each cell with #VALUE! in it, make it equal to the adjacent cell, so if #VALUE! is in column G, make it equal to the value of adjacent cell in column H and similarly, if #VALUE! is in column H, make it equal to the value of adjacent column G cell. Your spreadsheet should now look like the image below.
Our data is now ready for import into P6 but if we perform the import, P6 will throw up an error because the Start Day & Finish Day columns contain formulas. So we need to get rid of the formulas prior to importing and to do this we need to select all the Start Day & Finish Day data (in this example that will be cell range G3:H25), copy and then paste them back as Values.
Save & close the Excel spreadsheet and go to P6 and import the file. You will get a prompt that your import was successful and once you close that prompt, the Start Day & Finish Day columns in P6 will be populated as shown below.
To finalise our schedule, we need to hide the Start & Finish dates columns, save our layout and that is our schedule ready for issue/submission as shown below.
Method 2 - Using Global Change
This method assumes you are familiar and comfortable with using Global Change.
Create 3 User Defined Fields (UDFs), 2 of type Integer and name them Start Day & Finish Day and the third UDF is of type Start Date and name it Project Start Date. (This Project Start Date UDF is required since there is no way to access the Project Planned Start field under Activities subject in Global Change)
Next we add the newly created Start Day & Finish Day fields to the layout and save it.
Now we need to create 2 Global Change rules to populate our new Start Day & Finish Day fields but before we do that, enable the time part of dates (see Common Steps above) so that our dates look like 06-Jul-20 08:00.
Create the first Global Change for the Start Day field to look like the screenshot below.
The formulas in the 3 sections are:
If section:
This formula checks if the activity type is not a Finish Milestone (remember that in P6, Finish Milestones have no start dates, so we need to treat them differently)
and if the activity type is not a Finish Milestone, the formula in the Then section is calculated
but if the activity type is a Finish Milestone, the formula in the Else section is calculated
Then section:
We need to assign the Project Planned Start date to the the Project Start Date UDF and in this case it is 06-Jul-20 08:00. It is very important that the correct start time of the date is selected to avoid having decimals in our calculations.
With the Project Start Date now defined, we can calculate Start Day using the formula below which is spread over 2 lines in Global Change due to the limitations on number of parameters per line in Global Change
Start Day = Start - Project Start Date + 1
"+1" is so that our calendar day starts at Day 1 instead of Day 0
Else section:
We need to assign the Project Planned Start date to the the Project Start Date UDF and in this case it is 06-Jul-20 08:00. It is very important that the correct start time of the date is selected to avoid having decimals in our calculations.
For Finish Milestones, we get the Start Day value by using the formula below
Start Day = Finish - Project Start Date
Now run this Global Change and the Start Day field will be populated as shown in the image below.
Now, create the second Global Change for the Finish Day field to look like the screenshot below.
The formulas in the 3 sections are:
If section:
This formula checks if the activity type is not a Start Milestone
and if the activity type is not a Start Milestone, the formula in the Then section is calculated
but if the activity type is a Start Milestone, the formula in the Else section is calculated
Then section:
Just like in the first Global Change, we need to assign the Project Planned Start date to the the Project Start Date UDF and in this case it is 06-Jul-20 08:00 (this step shouldn't be necessary since the first Global Change should have populated this field but I have included it here just in case this Global Change is applied before the previous one)
With the Project Start Date now defined, we can calculate Finish Day using the formula below
Finish Day = Finish - Project Start Date
Else section:
Again, we need to assign the Project Planned Start date to the the Project Start Date UDF and in this case it is 06-Jul-20 08:00. (this step shouldn't be necessary since the first Global Change should have populated this field but I have included it here just in case this Global Change is applied before the previous one)
For Start Milestones, we get the Finish Day value by using the formula below, which is spread over 2 lines
Finish Day = Start - Project Start Date + 1
"+1" is so that our calendar day ends at Day 1 instead of Day 0 etc.
Now run this Global Change and the Finish Day field will be populated as shown in the image below.
Finally, we need to hide the Start & Finish dates columns, save our layout and our schedule should be ready for issue/submission as shown below.
Both Global Change rules are now available for use with future projects, but remember to set the Project Start Date for new projects.
Final Words
You will notice that WBS level details are not shown in either method and this is because for Integer type UDFs, P6 sums the values of the activities, when what we want at WBS level is minimum value for Start Day and maximum value for Finish Day. Due to this reason, my preference is to use Microsoft Project to produce an Ordinal Dates schedule using the method outlined in the Microsoft Project version of this tutorial.
