Microsoft Project packs more “bang for the buck” than most any other Microsoft Office application. With just a small amount of data input, the user can produce mountains of useful analysis.
Because MS Project shares many characteristics of a traditional database, users can take advantage of a myriad of pre-loaded database fields for storing information; but not every possible field for every possible need is supplied with the default setup. Users have the ability to add fields to the database by leveraging pre-loaded, generic, empty fields. MS Project contains generic fields for storing text, numbers, dates, flags, costs, etc…
Imagine tracking the length of time it takes to develop a prototype, train an employee, or process an order. MS Project has fields called “Start” and “Finish” to help track task duration, but users may wish to track durations with greater granularity and with more descriptive titles.
What follows is an example of utilizing custom fields to track start and finish dates as well as creating a custom field with a formula to calculate the duration.
Creating Custom Fields
To create new fields to store start and finish dates, select the Project tab and navigate to the Properties group and select Custom Fields.
In the Custom Fields dialog box, in the upper right corner, select the Type dropdown and select the appropriate field type. It is best to select a type that fits the purpose and characteristics of the data being stored. If you were to store a date, the “Start”, “Finish”, and “Date” fields are good candidates. If the date pertains to a start date, “Start” is a better choice than “Date” because it more accurately reflects the underlying purpose of the date. If you were storing someone’s birthday, then “Date” would be a better choice. In this example, “Start” would be a good choice for storing the start date of an event. The same would be said for storing finish date information in the “Finish” field type.
Interesting Note: Although you could store the date in a “Text” field (you can store anything in a “Text” field), the “Start”, “Finish”, and “Date” fields have the advantage of providing the user with a date-picker tool to input their date information.
Repeat this process to create a field to hold the finish dates. Set the Type to “Finish” and rename the “Finish1” field accordingly (i.e. “Training Finish”.)
Create a Custom Table to Display Custom Fields
To create a new table to display the new start and finish date custom fields, select the View tab and navigate to the Data group and select Tables. From the Tables dropdown, select More Tables…
In the More Tables dialog box, a new table can be created from scratch by selecting the button, but it is usually easier and faster to copy an existing table and modify the copy. In this example, a copy of the “Entry” table will be copied and modified to display the date information.
In the Table Definition dialog box, name the table accordingly (i.e. “Training Dates”) and add the “Training Start” and “Training Finish” custom fields to the list of table fields. Remove any unneeded fields form the list and click .
In the newly created custom table, enter a few tasks with start and finish dates.
Create the Date Difference Field
Using the same steps as defined earlier, create a new custom field to store the number of days between the custom start and custom finish dates. HINT: Use a “Number” type to store difference between the two dates. This is best because the answer is a number of days, not an actual date.
In the Formula for… dialog box, enter the following formula:
DateDiff(“d”, [Start1], [Finish1]) + 1
The DateDiff function will use the “d” parameter to calculate the number of days that occur between [Start1] and [Finish1]. Because no times are defined, the first day is not counted until the beginning of the second day is reached. Because of this behavior, the desired first day is not counted. To correct for this behavior, add 1 to the end of the calculation.
Add the DateDiff Field to the Custom Table
In the previously created custom table holding the start and finish information, click the small dropdown arrow to the right of the “Add New Column” heading.
From the list of the database fields, select the newly created custom number field.
Behold the calculated number of days between the start and finish dates.
Because the DateDiff formula in this configuration calculates the number of completed 24-hour periods till reaching the finish date, the non-working days (i.e. weekends) are included in the calculation. To omit the non-working days, use the ProjDateDiff function.
The ProjDateDiff function stores its result in minutes, so the result must be divided by 480 (the number of minutes in an 8-hour day) to convert the answer to days.