MS Project: How to Round Durations Up to the Nearest Whole Day

During the planning phase of a project, you may sometimes need to fine-tune the task durations based on a certain criterion. For instance, in the example below, the Text1 field’s formula rounds the duration values always up to the nearest whole day by using the Int function:

1452501300

If you want to overwrite the duration values with the rounded ones, then you can proceed as follows (note that we are dealing with auto-scheduled tasks):

  • Open the Custom Fields dialog box for the Text1 field and select None in the Custom attributes section. This action will keep the values calculated in the Text1 field but disable the formula referencing the field Duration.
  • Now you can copy the duration strings from the Text1 field to the Duration field, and then verify that all three fields show the identical values.

If you are working in an international project environment, then you may need to modify the formula for the team members using MS Project versions in other languages. Then instead use the following formula:

Text2 field’s formula:

ProjDurConv( [Minutes Per Day] * Abs(Int(-[Duration]/[Minutes Per Day])),

pjDays )

If MS Project’s setting for Duration is entered in is set to <Days>, then there is no need to use the second parameter pjDays in the function.

Suppose that you send the file to a team member in France. The team member opens the file, hits <F9> and then this is what MS Project’s French version shows:

716801562

In the French version of MS Project, the formula box for the Text2 field shows the formula:

ProjDurConv([Minutes par jour]*Abs(Int(-[Durée]/[Minutes par jour])),pjDays)

As it is seen above, MS Project displays the field names and the duration units in French automatically; there is no need to modify the formula in the Text2 field for the other language versions of the product. But you need to input the field names in French, while entering the formula to a custom text field in the French version of the product. Interestingly, some field names in English are recognized and automatically converted to the ones in the current language while entering a formula, but the field name [Minutes Per Day] is not one of them. The function names ProjDurConv, Abs and Int, and the constant name pjDays, all are the keywords, therefore, they always remain the same. Also note that the time unit labels in ProjDurConv‘s output are in the product’s language.

A note on rounding the numbers: do not use the Round function (it works but not listed in the Function button menu) to do arithmetic rounding since it performs banker’s rounding.

You can read the whole story on rounding numbers in the ebook Mastering Custom Field Formulas in MS Project.

 

162345311

The content above applies to all versions of the product supporting the custom fields feature.

© Copyright – Ismet Kocaman | http://www.ismetkocaman.com

782264219

Advertisements