Mastering Custom Field Formulas in MS Project | Articles


By Ismet Kocaman

 

 

 

Handling Seconds in MS Project

 

Version 1, Published on October 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.

 

The date and time information stored in a date field always contains the seconds part of the time value. On the other hand, we cannot manage the seconds part of the time values while working with MS Project. We will now discuss how it works, based on the information presented by MS Project.

 

The duration fields store the values in minutes, therefore all the duration entries in time units other than minutes are automatically converted to minutes and stored in the duration fields. Even though MS Project allows us to enter decimal duration values with a precision up to one-tenth of a minute, the values entered are rounded off to the nearest whole minute and then stored in the duration fields; and the smallest value that a duration field holds is 1 minute.

 

The following table shows some duration entries in the Duration field and the corresponding values stored, which are obtained by using a custom number field formula referencing the Duration field:

 

 

Depending on user’s choice of the time unit while entering a value, the Duration field may display durations in various units, but it will always store the values in minutes in the background, as mentioned above. In order to be more accurate on this matter, let us rephrase this sentence as follows since we do not know the technical details on how the duration value is actually recorded into the Duration field in the background: depending on user’s choice of the time unit while entering a value, the Duration field may display durations in various units, but we will always get a value representing the duration amount in minutes when we refer to a duration type field in a formula.

 

Therefore, the formula in the Number1 field will always return the values in whole minutes, regardless of the value and the time unit shown in the Duration field. Note that the custom number field Number1 does not display the time units.

 

In MS Project, the date fields can store time values with a precision up to one-tenth of a minute, which corresponds to seconds in multiples of 6 such as 6, 12, 18, 24, 30, 36, 42, 48 and 54 (that is, the seconds obtained by multiplying 0.x minutes with 60 seconds/minute). In order to demonstrate this behavior, we can use several custom field formulas as also shown above, since MS Project does not display the seconds in the date fields.

 

The custom text field formulas in Text1 and Text2 convert the time values to text. In order to verify whether the duration of time between the start and the finish of the task matches the duration value entered, Number2 field’s formula calculates the number of seconds between Start and Finish, and then divides the calculated seconds by 60 to display the results in minutes. As it is seen in the table, the values in the Duration field match the corresponding ones in the Number2 field, therefore we conclude that MS Project does not ignore seconds in date fields when calculating task’s finish date and time, based on the decimal duration value entered, as it is also seen from the second values listed in the Number3 field.

 

So the question now is that what happens if we try to store a date and time value with the seconds which is not in multiples of 6; e.g. 08:00:21 AM ? MS Project always ignores the seconds part in the time entry, therefore we cannot manually feed the seconds to a date field. But we can input the time value indirectly, by either importing or entering as a formula, to a custom date field.

 

In the example below, various time values with seconds are passed to custom date fields through formulas and then the formulas in Date1 and Date2 are deleted. As shown in the table below, the custom date field Date1 displays the time 08:00 AM for the entry 08:00:21 AM. A custom text field formula in Text2 shows the time value stored in that field, which is 08:00:24 AM.

 

The Date1 field’s formula also includes a date value otherwise returning only a time value to the field generates #ERROR. In the Text2 field’s formula, the time value output from the TimeValue function is converted to a string implicitly by the concatenation with the zero-length string in order to avoid NA in the custom text field.

 

Note on the table that seconds part of all the time values stored (which is possible to display only by using a custom text field formula) in the date field are in multiples of 6 regardless of the actual seconds entered (also see Date2 and Number1). And also note that we do not know the algorithm behind, for example, how the entry 08:00:21 AM (or 08:00:35 AM) is stored as 08:00:24 AM (or 08:00:36 AM) in the custom date field.

 

As the examples above clearly indicate, we cannot handle the time values with a resolution down to one second even if we somehow manage to store a certain time value with the seconds into a custom date field.

 

Even though MS Project does not accept and display the seconds, schedule modifications might “somehow” produce the seconds value in the tasks’ start or finish times. The schedule below has been created in order to demonstrate this behavior. The Text1 field’s formula TimeValue([Start])&”” shows the seconds in the tasks’ start time:

 

 

Note that both T1 and T3 show start times as 08:00 when MS Project’s date display format is set to <12:33>. Therefore, the formulas comparing the start times in the schedule above may return inaccurate results, if the comparison operations exclude the seconds in times.

 

 

Example - Scheduling in Whole Seconds

 

In this example, a set of custom field formulas will be developed and used to schedule a sequence of operations with durations in whole seconds. In our scenario, we will conduct a lab experiment and we want to find the total duration of a sequence of operations in the experiment by using MS Project. Suppose that we have the process data from the previous experiments, so we do not need to estimate the durations. The schedule will not be used for resource and cost management, and for tracking purposes. Steps and details are as follows:

 

  • Open a blank project plan file and set MS Project’s options to accept durations in minutes by default and to display only the time values in the date fields; also set the project calendar to the base calendar 24 Hours.

  • A project start date, for example, 12/12/2020 00:00 can be entered, but let us keep the default start date and time which is the current date and 08:00 AM, and also keep the default setting for the Default start time which is 08:00 AM.

  • Set the timescale display format to one tier of minutes counting from the project start, one by one (that is, Count = 1). In order to avoid confusion, it is important to select a display format which does not include any of the minute labels since we will use the timescale as if the numbers on the timescale represent periods in seconds, but not in minutes.

  • We will work with auto-scheduled tasks. Create the task list shown in the picture by entering the task names to the Task Name column and the corresponding durations in whole minutes to the Duration field, and apply the lag/lead times in whole minutes to the task dependencies in the Predecessors field, as shown in the table below:

  •  

 

  • Insert the custom text fields Text1, Text2 and Text3 to the table and modify the column titles to display Text1 (DURATION), Text2 (START) and Text3 (FINISH).

 

We will now create new columns of project data representing a schedule in seconds. In this schedule, one minute will represent one second. We will use some task custom text field formulas in order to introduce a scale factor of 1/60 to the existing duration values; and then recalculate the times by using the adjusted (or scaled) duration values. Let us proceed with the steps below:

 

  • First, we will display the actual minutes (that is, not adjusted) with the seconds label “sec” in a custom text field (Text1) by using the formula below:

  •  

    Text1 field’s formula:

     

    [Duration] + " sec" + iif( [Summary]," (" & Format( [Duration]/60,"Fixed") & " min)","")

     

    or a more readable formula

     

    CStr([Duration]) & " sec" & iif( [Summary]," (" & Format( [Duration]/60,"Fixed") & " min)","")

     

    Regarding the first part of the formula, that is, CStr([Duration]) & " sec"; in our model, 1 minute of duration value entered to the Duration field will correspond to 1 second of the adjusted duration value displayed in the Text1 field; therefore, the Text1 field displays the same numeric values converted to text strings with the label “sec”. We do not need to make any adjustment on the original duration numbers entered.

     

    Regarding the other part of the formula, calculated only for the summary lines; we will consider the value entered as duration in seconds, and then divide it by 60 in order to obtain the adjusted duration value in minutes. The results with “min” labels will be displayed on the summary rows. In the formula, the Format function displays two digits to the right of the decimal separator in the output when the predefined numeric format name “Fixed” is used in the format expression.

 

  • We will now discuss how to calculate the start times including seconds by using the adjusted periods of the timescale:

 

    Text2 field’s formula:

    CStr(TimeValue([Project Start]) + (([Start] -[Project Start])*24*60/60)/24/60)

     

    The details on the formula are as follows: the numeric expression ([Start]-[Project Start])*24*60 gives the number of calendar minutes between the project’s start and a task’s start. We multiply the result by the scale factor 1/60, and then by 1/1440 (i.e. 1/24/60) in order to obtain the time serial number which is next to be added to the project start’s time serial returned by the TimeValue function. The resulting time value is converted to string explicitly by using the CStr function in order to display in the custom text field, otherwise the field will show NA.

     

    The Text2 field will show the AM label along with the times when the system’s time display format is 12-hour. Therefore, it is better to use the Format function instead of CStr, as shown below:

     

    Text2 field’s formula modified:

    Format( TimeValue([Project Start])+ (([Start]-[Project Start])*24*60/60)/24/60, “hh:nn:ss” )

     

    Here, the format expression “hh:nn:ss” represents 24-hour time display format.

 

  • Next, we will create a formula to display the adjusted finish times including seconds. This time, let us use the Format function as below:

 

    Text3 field’s formula: 

    Format(TimeValue([Text2]) + [Duration]/60/24/60, "hh:nn:ss")

     

    In the formula above, the task’s duration is multiplied by the scale factor 1/60 and converted to a time serial value (i.e., multiplied by 1/24/60 or 1/1440) and then added to the time serial value of the task’s start time which is stored in the Text2 field, as a text string representing time including seconds.

 

Note that we also need to display the values on the summary rows; this means that all the formulas should be calculated for the summary tasks as well, therefore adjust the related settings accordingly while entering the formulas in the Custom Fields dialog box. That is, select the option Use formula in the Calculation for task and group summary rows section of the Custom Fields dialog box. The picture below shows the Gantt Chart view of the resulting schedule; the total duration of the operation sequence scheduled in this experiment, as calculated by MS Project, is 7 seconds or 0.12 minutes:

 

 

Using different font-color formatting in the columns and arranging the order of the columns in the table would definitely help in distinguishing the actual entries from the scaled values. Also beware that the “min” labels in the Predecessors field represent the seconds in our model; here, we will leave the Predecessors field as it is, since the list may contain more than one lag or lead, and that makes replacing min(s) to sec(s) a rather difficult task by a formula. And do not enter any fractional values for duration and lag/lead minutes otherwise the times calculated won’t be accurate since MS Project stores duration minutes as whole numbers.

 

You wouldn’t probably need a scheduling fidelity of seconds in a project where the tasks are performed by people, but some implementations such as satellite system scheduling requires even a fidelity of milliseconds.

 

 

 


Revision History:

Version 1 - October 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.