Mastering Custom Field Formulas in MS Project | Articles


By Ismet Kocaman

 

 

 

Displaying Summary Durations in Elapsed Units

 

Version 1, Published on November 12th, 2015

Please first see Disclaimer, Copyright Notes and Trademark Notes sections on the bottom of this page. This document is related to all

standalone desktop versions of MS Project Professional/Standard supporting the custom field formulas feature.

 

 

Shown below is a short sequence of auto-scheduled tasks, all linked with finish-to-start dependency below a summary task. The task durations have been entered in elapsed months, but MS Project calculates the summary task's duration in days, based on its default duration unit setting.

 

           

            Note    MS Project’s default duration time unit is the time unit specified in the box Duration is entered in on the Schedule tab of the Project Options dialog box. It is set to “Days” if MS Project’s default settings have not been changed.

 

Suppose that we want to display the summary task's duration in elapsed months as well. It is not possible to modify the settings so that the summary tasks show durations in elapsed months. For this purpose, we can create a custom field formula converting summary task durations from days (or any other time unit) to elapsed months. First, we need to calculate the elapsed minutes for the summary task. The formula,

 

([Finish] - [Start]) * 24 * 60

 

where the difference between the start and the finish serial numbers is multiplied by the number of minutes elapsed in a day (that is, 24 * 60), returns the elapsed minutes between a task's start and finish, given that the time part is expressed as a fraction of a 24‑hour day in a date and time serial number.

 

The DateDiff function can also be used to calculate the elapsed minutes between the two dates as in DateDiff("n", [Start], [Finish]). The DateDiff function ignores calendars unlike the ProjDateDiff function which calculates the time difference, based on the working times defined in the project calendar or in a task calendar applied to the task, by default, when we do not specify any other calendar in the parameter list.

  

As it is seen in the table below, the Text1 field using the formula given above, displays the task durations in elapsed minutes. The custom text field Text2 inserted into the table contains a reference to the Duration field, so it displays the duration minutes stored in the Duration field.

 

Task table

 

 

Task Name

Duration

Text1

Text2

Summary

172 days

345600

82560

Task #1

1 emon

43200

43200

Task #2

2 emons

86400

86400

Task #3

5 emons

216000

216000

Formulas:

Text1 field       : ([Finish] - [Start]) * 24 * 60

Text2 field       : [Duration]

 

In order to evaluate the formulas for both the subtasks and the summary tasks, the option Use formula must be selected in the "Calculation for task and group summary rows" section of the Custom Fields dialog box, otherwise the formulas will not be calculated for the summary tasks and therefore we will not see any value in the custom fields for the summary tasks.

 

Note that the numeric results (that is, the number of minutes) of the formulas are displayed and stored as text information in the custom text fields without an error, even though there is no explicit number-to-text conversion in the formulas by using a type conversion function such as CStr. Although it does not apply to all situations, MS Project automatically handles the conversions here.

 

In the table, the elapsed minutes calculated and displayed in the Text1 field for a subtask matches the corresponding duration value shown in the Text2 field. This is because the duration values entered in elapsed months are converted to, and stored as, minutes in the Duration field, which is referenced by the Text2 field’s formula. On the other hand, there is no match at the summary level since MS Project calculates the summary task duration based on the working times defined in the project calendar (here, it is the Standard base calendar). The Text2 field shows the value calculated and stored in the Duration field for the summary task, which is 82560 working minutes. The 82560 working minutes correspond to 172 working days as displayed in the Duration field (82560 minutes / [Minutes Per Day] or 82560 minutes / (8 * 60) = 172 days).

 

The Text1 field's formula calculates the elapsed minutes for the summary task to be 345600. An elapsed month has always 30 elapsed days, therefore the 345600 elapsed minutes correspond to 8 elapsed months, as calculated below:

 

            Elapsed minutes          >        345600 minutes

            Elapsed hours              >        345600 / 60 = 5760 hours

            Elapsed days                >        345600 / 60 / 24 = 240 days

            Elapsed months           >        345600 / 60 / 24 / 30 = 8 months

 

We can modify the Text1 field's formula to calculate the summary durations in elapsed months directly; see the results below:

 

Task table

 

Task Name

Duration

Text1

Summary

172 days

8 emons

Task #1

1 emon

 

Task #2

2 emons

 

Task #3

5 emons

 

Formulas:

Text1 field  :  iif ( [Summary],

([Finish] - [Start]) / 30  & " emons", "")

 

 

The following arithmetic expressions containing hardcoded conversion factors convert the difference between the start and the finish serial numbers to durations in various elapsed units:

 

            To calculate this > Use the expression below

Elapsed Minutes > ([Finish] - [Start]) * 24 * 60

            Elapsed Hours > Elapsed Minutes / 60 or ([Finish] - [Start]) * 24

Elapsed Days > Elapsed Hours / 24 or ([Finish] - [Start])

Elapsed Weeks > Elapsed Days / 7  or ([Finish] - [Start]) / 7

Elapsed Months > Elapsed Days / 30 or ([Finish] - [Start]) / 30

 

The summary task durations can also be converted to elapsed months by using the ProjDurConv function. We can insert either formula below, into a custom text field to display the duration in elapsed months:

 

ProjDurConv( [Text1] * 1, pjElapsedMonths ) or ProjDurConv( [Text1] & "mins",pjElapsedMonths)

 

where the Text1 field contains the formula ([Finish] - [Start]) * 24 * 60

 

Note that the expression [Text1] * 1 is a numeric expression which evaluates to a numeric value (that is, duration amount in elapsed minutes) that the function expects as the first argument. The multiplication by 1 forces the text-to-number conversion explicitly, otherwise the function cannot recognize the Text1 field’s text content because there is no time unit label (i.e., a valid time unit abbreviation) included with it. We can also use a type conversion function to perform an explicit text-to-number conversion as in,  

ProjDurConv( CLng( [Text1] ), pjElapsedMonths )

 

provided that the Text1 field contains nothing but all numbers as characters in the text information stored. The expression [Text1] & "mins" is a string expression where the concatenation operator (i.e., the ampersand, “&”) combines the Text1 field’s content with the string literal “mins” to produce the duration string that the function expects.

 

Let us now go back to the first example. The results of the new formulas are shown below, and note that the text output from the ProjDurConv function includes a time unit:

 

Task table

 

 

 

Task Name

Duration

Text1

Text2

Text3

Summary

172 days

345600

82560

8 emons

Task #1

1 emon

43200

43200

1 emon

Task #2

2 emons

86400

86400

2 emons

Task #3

5 emons

216000

216000

5 emons

Formulas:

Text1 field       : ([Finish] - [Start]) * 24 * 60

Text2 field       : [Duration]

Text3 field       : ProjDurConv( Text1*1, pjElapsedMonths )

 

We can improve the duration conversion process by defining a lookup list to select an elapsed time unit from (see the Text7 field below) and by creating a formula (see the Text8 field below) feeding the PjFormatUnit constants to the ProjDurConv function based on the item selected from the dropdown list as shown below:

 

  

Text7 field :      Define the lookup table as shown below.

 

Note  While creating the lookup table, clear the checkbox “Allow additional items to be entered into the fields. (Values will be added to lookup)” in the “Data entry options” section of the Edit Lookup Table dialog box, otherwise any text accidentally entered into the Text7 column will be added to the lookup list.

Row

Value

1

Elapsed Minutes

2

Elapsed Hours

3

Elapsed Days

4

Elapsed Weeks

5

Elapsed Months

 

Text8 field’s formula    :

 

iif( [Summary], ProjDurConv( ([Finish] - [Start]) * 24 * 60, switch(

[Text7]="Elapsed Minutes", pjElapsedMinutes,

[Text7]="Elapsed Hours", pjElapsedHours,

[Text7]="Elapsed Days",  pjElapsedDays,

[Text7]="Elapsed Weeks", pjElapsedWeeks,      

[Text7]="Elapsed Months", pjElapsedMonths )), "")

 

Note that PjFormatUnit constants returned by the Switch function are the predefined names corresponding to certain numerical values. While evaluating the formula, MS Project recognizes them and replaces each one with the corresponding numerical value. Names are easy to remember than the numbers, so using names corresponding to numbers eliminates the need for referring to the function description, whenever we enter a reference to the ProjDurConv function in a formula. If you like, you can use the corresponding numerical values directly in the Switch function. To find out what the value is, for example, enter pjDays into the formula box for a custom number field; the field will display the number 7. Then enter 7 in place of pjDays in the formula.

 

Note    Search online for the title “PjFormatUnit enumeration” in the Project Developer Reference on the website for the product help, in order to see a list of the numeric values corresponding to pjFormatUnit constants.

 

The formula ProjDurConv( "1 emo", pjElapsedDays ) returns 30 edays to a custom text field, which is the number of elapsed days in an elapsed month. In the real-world calendar, the number of days in a month varies from month to month. Therefore, a duration entry of one elapsed month for a task might not match the time elapsed in a real-world month. For example, a task scheduled to start on February 1st, 2014 00:00 and to finish on March 1st, 2014 00:00 would have a duration of 0.93 elapsed months (28/30 = 0.93) as shown in the picture below:

 

 

            Formula:

            Text1 field       : ProjDurConv(

    DateDiff( "n", "Feb 1, 2014 00:00" , "Mar 1, 2014 00:00"),

    pjElapsedMonths ) or ProjDurConv( "28 edays", pjElapsedMonths )

 

This situation can be eliminated by expressing elapsed durations in elapsed time units other than elapsed months such as elapsed days and elapsed weeks.

 

In this example, we have discussed the time unit conversions on the duration values entered or calculated, and how to use the ProjDurConv function in converting the duration values.

 

 


Revision History:

Version 1 - November 12th, 2015 - Initial document


 

 

 

Disclaimer

The information contained in this document is intended only for the general interest of its readers and should not be used as a basis for making any business or other important decisions. Though all the efforts have been made to create accurate content, mistakes can occur. The author of this document cannot, therefore, guarantee the accuracy of content. The author of this document disclaims all warranties and must advise you to use this document at your own risk. The author of this document is not liable for loss of any nature resulting from the use of or reliance upon the information found therein.

 

Copyright Notes

All Content Copyright © Ismet Kocaman | ikocaman.pm[at]gmail.com | No part of this document may be reproduced, stored in a retrieval system or transmitted in any form or by any means, without the prior written permission of the Author (Ismet Kocaman). Do not quote/refer to the parts of the document and do not use it as training material without permission. 

 

Trademark Notes

Microsoft® is a registered trademark or trademark of Microsoft Corporation in the United States and/or other countries. The author of this document has no affiliation with Microsoft Corporation. All other trademarks mentioned herein are the property of their respective owners. Screen captures were used with authorization from Microsoft Corporation. This document is not a product of Microsoft Corporation.