Show ordinal dates instead of Start and Finish dates in Microsoft Project

Sometimes during tender or bid submission for a project, the client might not provide a Project start date and bidders would be required to show ordinal dates in the Gantt chart timeline and calendar days in the start and finish dates columns, so that, for each activity, the schedule will show start and finish days, e.g. for a 10-day activity, the schedule will show day 1 in the start column and day 10 in the finish column.

This tutorial will show you how to set-up your Microsoft Project schedule to display calendar days. For this tutorial, I'm using Project 2010 but I believe it should work for later versions too.

Assuming we have the schedule shown below with normal dates in the start column, finish column and timescale.

Figure 1: Example Microsoft Project View

Two key schedule assumptions are:

The schedule uses an 8-hour day and 7-day week calendar
The Effective Date (first milestone in schedule) has same date as Project Start Date entered in the Project Information dialog)

Easy Peasy

We would start with the easy part, changing timescale dates to ordinal dates.

Double click any part of the timescale to open the Timescale dialog.

Under the Timescale options select One Tier (middle) from the Show dropdown. Under the Middle tier formatting, select Months (or your preferred unit) from the Units dropdown and then select Month 1, Month 2, ... (from Start) from the Label dropdown. Click OK to complete the timescale formatting and close the dialog.

You should now notice that the timescale dates have changed from calendar dates to serial month numbers.

Dates to Days Transformer - Activities

That is the first part of our problem solved, next task is to change start date to start day and finish date to finish day through the use of Custom Fields. Now add 2 new columns to your schedule, Number 1 and Number 2.

With your cursor in a cell under Column Number 1, click Format tab in the ribbon and then Custom Fields in the Columns group and this will open the Custom Fields dialog.

Select Number 1 under Field list and click on Rename button, enter Start Day in the New name for 'Number 1' textbox of the Rename Field dialog and then click OK (arrows 9 to 12). Repeat the rename steps (arrows 9 to 12) for Number 2 field but this time, the new name should be Finish Day so that the list looks like arrow 13.

Next, select the Start Day field and click on Formula in the Custom attributes group. Enter the formula [Start] - [Project Start] + 1 in the Edit formula dialog and click OK

Figure 7: Adding formulas to custom number fields

When prompted to replace all data in the Start Day field, click OK.

Figure 8: Prompt to replace Start Day column data

Under the Calculation for task and group summary rows, tick the Use formula checkbox.

Figure 9: Calculation for Start Day summary rows

Now select the Finish Day field and click on Formula in the Custom attributes group. Enter the formula ([Finish] - [Project Start] + 1) - 9/24in the Edit formula dialog and click OK. The 9/24 is used so that we do not have decimals in our Finish Day field. Our 8-hour day calendar starts from 08:00 to 17:00 which is 9 hours and 24 represents the total hours in a day.

Figure 10: Prompt to replace Finish Day column data

When prompted to replace all data in the Finish Day field, click OK.

Under the Calculation for task and group summary rows, tick the Use formula checkbox.

Figure 11a: Calculation for Finish Day summary row

Now click on OK to close the Custom Fields dialog.

The schedule should now have numbers in the Start Day and Finish Day columns (arrow 22). Now hide Start and Finish columns (arrows 23).

Figure 13: Display Start & Finish Days columns

So our final schedule with ordinal dates should look like below.

Figure 14: Schedule view with ordinal dates

Our schedule looks almost perfect except for milestones, which have decimals in the Start Day and Finish Day columns. If your schedule does not have milestones, you would not have this problem but any schedule worthy of its name should have at least two milestones, therefore we would need to get rid of the decimals.

Dates to Days Transformer - Activities and Milestones

You will notice that Start Day for start milestones (milestone at the start of a task) do not have the decimal issue while Finish Day for finish milestones (milestone at the end of a task) do not have this decimal issue too.

Unlike Primavera P6 which explicitly distinguishes between start and finish milestones, Microsoft Project does not have that feature but there is a workaround using Custom Fields. Based on our definition above, start milestones should occur at 08:00 and finish milestones at 17:00, the start and finish times respectively of the calendar in use in this example schedule. What we need to do now is find a way to extract this time element from dates.

Now add a new column, Text 1, to your schedule and then open the Custom Fields dialog. Rename Text 1 field to Milestone Type and then enter the following formula for this field.

IIf(([Duration]=0) And (Right([Finish],8)="17:00:00"),"Finish Milestone",IIf(([Duration]=0) And (Right([Start],8)="08:00:00"),"Start Milestone",""))

Click OK if you see a prompt that all data in Milestone Type field will be replaced. Our schedule should now show start and finish milestones.

Figure 16: Display Milestone Type column

Now that we can distinguish between start and finish milestones, we need to update the Start Day and Finish Day formulas so that we can get rid of decimals. Change the Start Day formula to

IIf([Milestone Type] ="Finish Milestone",([Finish]-[Project Start]+1)-9/24,([Start]-[Project Start]+1))

Figure 17: Modified Start Day column formula

Also, update the Finish Day formula to

IIf([Milestone Type] ="Start Milestone",([Start]-[Project Start]+1),([Finish]-[Project Start]+1)-9/24)

Figure 18: Modified Finish Day column formula

Close the Custom Fields dialog and you will notice that we no longer have decimals in either Start Day or Finish Day columns.

Figure 11: Start & Finish Days columns without decimals

Now hide the Milestone Type column and the schedule columns should be as desired.

But if you look at the Gantt chart section of our schedule, you will notice that the milestones still have normal dates beside them. We are going to change these dates to ordinal dates too and to do this,

click on the Format tab on the ribbon and then Format in the Bar Styles group and then click on Bar Styles in the dropdown to open the Bar Styles dialog.

Figure 22: Configure Milestone Bar Style

In the Bar Styles dialog, select Milestone under Task (arrow 31) and in the bottom section of the dialog, click on Text tab and in the Left dropdown (arrow 32), select Start Day (arrow 33) and click OK to close the dialog and return to the Gantt chart.

Figure 23: Finalised schedule with ordinal dates

The schedule is finally ready for submission with only ordinal dates on display. Remember to save the schedule.

Final Word

You do not need to repeat all the steps in this tutorial for future ordinal dates schedules. Use Organize Global Template to copy all the Custom Fields we created in this tutorial into the global template so that you can reuse them in other files. Remember to leave a comment below!!!

Addendum 08-May-20

Check the following if you still find yourself with decimal points

Project Start Date in the Project Information dialog (select Project tab from ribbon and click on Project Information) should have a time part of 08:00 and not 00:00
In a Summary section with milestones, the first activity should be a Start Milestone and last activity should be a Finish Milestone or an activity with duration of 1 day or more
Elapsed duration (edays) will produce decimal points. I'd recommend your normal duration (days) with a 7-day calendar to produce same output as elapsed days but without the decimal point.