Shown below is
a short sequence of autoscheduled tasks, all linked with finishtostart
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 numbertotext 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
texttonumber 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 texttonumber 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 realworld
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 realworld month. For example, a task scheduled to start on
February 1^{st}, 2014 00:00 and to finish on March 1^{st}, 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.