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 onetenth 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 onetenth 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 zerolength 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
autoscheduled 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:
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.
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 12hour. 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
24hour time display format.
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 fontcolor 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
