Microsoft Project – Calculate Days Between Two Custom Dates

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.

SNAGHTML1cb770

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.

image

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.

From the Field list, select “Start1” and click the image button.  Rename the field to reflect its purpose (i.e. “Training Start”) and click image.

image

No special options will be utilized in this field, it will only be used to accept dates from the user, so click image to close the Custom Fields dialog box.

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”.)

image

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…

SNAGHTML3aa9b3

In the More Tables dialog box, a new table can be created from scratch by selecting the image 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.

image

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 image.

image

In the newly created custom table, enter a few tasks with start and finish dates.

SNAGHTML459d78

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.

image

In the Custom Attributes section of the Custom Fields dialog box, select the radio button to the left of the image button.

image

In the Formula for… dialog box, enter the following formula:

DateDiff(“d”, [Start1], [Finish1]) + 1

image

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.

Click image twice to close the dialog boxes.

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.

SNAGHTML77a10f

From the list of the database fields, select the newly created custom number field.

SNAGHTML67ec01

Behold the calculated number of days between the start and finish dates.

SNAGHTML6981f2

BONUS FORMULA

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.

ProjDateDiff([Start1],[Finish1],[Project Calendar])/480

image

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.

SNAGHTML72454b