You use this screen to define a formula for your production, text and
date fields.
The following functions are currently supported by DTS.
| Function
|
Usage
|
Description
|
Example
|
| - * / + |
|
These are the operators minus,
multiply, divide and plus |
|
|
^ |
|
Raise a number to the power of another
number |
2 ^ 5 = 32 |
| ABS |
ABS(Number) |
Returns the absolute value of a number |
ABS(-5) = 5 |
| ACOS |
ACOS(Number) |
Returns the arccosine of a number |
|
| ASIN |
ASIN(Number) |
Returns the arcsine of a number |
|
| ATAN |
ATAN(Number) |
Returns the arctangent of a number |
|
| CEIL |
CEIL(Number) |
Rounds a number to the nearest integer |
CEIL(3.7) = 4 |
| COS |
COS(Number) |
Returns the cosine of a number |
|
| COSH |
COSH(Number) |
Returns the hyperbolic cosine of a
number |
|
| CURDATE |
CURDATE() |
Returns the current date as a Julian
date, i.e. number of minutes since 1980-01-01 |
|
| DATEADD |
DATEADD(Interval,Number,DateValue,Calendar) |
Adds a duration (specified by
Interval and Number) to a date (specified by
DateValue) using Calendar to determine working
time. The return value is a Julian date, i.e. number of minutes
since 1980-01-01.
Interval can be the following:
0 or "n" representing minutes
1 or "h" representing hours
2 or "d" representing days
3 or "w" representing weeks
4
or "m" representing months
5 or "y" representing years
Number is a double value representing the number of
intervals to add.
Calendar can be a field, or a
string representing the calendar name. If the string is empty,
i.e. "", then the calculation is done in elapsed time and not
according to a scheduling calendar. |
DATEADD("d",3.5,[Start],[Calendar]) will add 3.5 days to the
start date of a task based on the task's calendar (jumping over
non-work periods).
DATEADD(2,5,[Start],"") will add 5 days to
the start date of the task. This is in elapsed time, i.e. 5 days
x 24 hours, and ignores the task calendar. |
| DATEDIFF |
DATEDIFF(Date1,Date2,Calendar,OutputInterval) |
Returns a double value representing the
difference between 2 dates (Date1 and Date2)
according to a Calendar in a time unit determined by
OutputInterval.
See DATEADD above for details on the
Calendar and I parameters. |
DATEDIFF([Start],[Finish],[Calendar],"d") return the
equivalent of the task's duration in days
DATEDIFF([Start],DATEADD("m",1,DATESERIAL(YEAR([Start]),MONTH([Start]),1,0,0),""),[Calendar],"d")
returns the number of days between a task's start date and the
end of the month in which the start day falls. |
| DATESERIAL |
DATESERIAL(Year,Month,Day,Hour,Minute) |
Returns a Julian
date (number of minutes since 1980-01-01) based on the integer
parameters. |
|
| DAY |
DAY(DateField) |
Converts a julian date (DateField,
e.g. DTS start or finish dates) to a day of the month |
|
| DCOUNT |
DCOUNT(TaskFilterName) |
Returns the number of tasks that
conform to the task filter specified by TaskFilterName. Using
this function can significantly slow down the recalculation of
task formulas. |
|
| DEGREES |
DEGRESS(Number) |
Converts radians to degrees |
DEGREES(3.14159) = 180 |
| EXP |
EXP(Number) |
Returns "e"
raised to the power of a given number |
EXP(1) = 2.718282 |
| FIND |
FIND(Find_Text,Within_Text,Start_Num) |
Returns the offset of one text value
within another, starting its search at a certain character
offset. Returns -1 if the text cannot be found. |
FIND("abc","12345abcde",3) = 5
FIND("abc","12345abcde",7)
= -1 |
| FLOOR |
FLOOR(Number) |
Rounds a number down to the nearest
integer |
FLOOR(3.7) = 3 |
| IIF |
IIF(logical_test,value_if_true,value_if_false) |
Check whether a condition is met, and
returns one value if the condition is true, and another value of
the condition is false. |
IF([Name]="abc",10,20) will return 10 if Name="abc",
otherwise it will return 20.
IF([Tons]>100,5,0) will return 5
if Tons>100, otherwise it will return 0. |
| ILOOKUP |
ILOOKUP(Lookup_Table, Lookup_Value) |
Returns a value from Lookup_Table
based on the input value Lookup_Value. If a lookup
value falls inbetween the actual lookup values in the table, the
return value is interpolated linearly between the respective
lookup values. |
Assume following lookup table called ILT:| Lookup value | Return value | | 1 | 10 | | 2 | 20 | | 3 | 30 | | 4 | 40 | | 5 | 50 |
VLOOKUP("ILT",2) = 20
VLOOKUP("ILT",2.5) = 25 |
| LEFT |
LEFT(Text,Num_Chars) |
Returns the first Num_Chars
characters from a text value |
|
| LEN |
LEN(Text) |
Returns the number of characters in a
text string |
|
| LN |
LN(Number) |
Returns the natural logarithm of a
number |
|
| LOG10 |
LOG10(Number) |
Returns the base-10 logarithm of a
number |
|
| LOWER |
LOWER(Text) |
Converts Text to lowercase
text |
|
| MAX |
MAX(Number,Number,...) |
Returns the maximum value of a list of
up to 99 values or production fields |
|
| MID |
MID(Text,Start_Num,Num_Chars) |
Returns a specific number of characters
(Num_Chars) from a text string (Text) starting
at the position you specify (Start_Num) |
|
| MIN |
MIN(Number,Number,...) |
Returns the minimum value of a list of
up to 99 values or production fields |
|
| MONTH |
MONTH(DateField) |
Converts a julian date (DateField,
e.g. DTS start or finish dates) to a month |
|
| PARSE |
PARSE(Text,Separator_Tokens,Substring_Number) |
|
|
| PRJSTART |
PRJSTART() |
Returns the schedule start date as a Julian
date, i.e. number of minutes since 1980-01-01 |
|
| PROPER |
PROPER(Text) |
Capitalizes the first letter in each
word of a text value |
PROPER("abc def") = "Abc Def" |
| RADIANS |
RADIANS(Number) |
Converts degrees to radians |
RADIANS(90) = 1.57 (=Pi/2) |
| RIGHT |
RIGHT(Text,Num_Chars) |
Returns the Num_Chars
rightmost characters from a text value |
RIGHT("abcde",2) = "de" |
| ROUND |
ROUND(Number,Num_Digits) |
Rounds a number to a specified number
of digits |
|
| SIGN |
SIGN(Number) |
Rounds a number to a specified number
of digits |
|
| SIN |
SIN(Number) |
Returns the sine of the given angle (in
radians) |
|
| SINH |
SINH(Number) |
Returns the sine of the given angle |
|
| SQRT |
SQRT(Number) |
Returns a positive square root |
SQRT(9) = 3 |
| TAN |
TAN(Number) |
Returns the tangent of a number |
|
| TANH |
TANH(Number) |
Returns the hyperbolic tangent of a
number |
|
| TEXT |
TEXT(Number,Format_String) |
Formats a number and converts it to
text |
|
| TRIM |
TRIM(Text) |
Removes spaces from the beginning and
end of some text |
TRIM(" abc ") = "abc" |
| UPPER |
UPPER(Text) |
Converts text to uppercase |
UPPER("abc") = "ABC" |
| VALUE |
VALUE(Text) |
Converts a text argument to a number |
VALUE("3.14") = 3.14
VALUE([UserTextField]) = 3.14 (if
UserTextField = "3.14) |
| VLOOKUP |
VLOOKUP(Lookup_Table, Lookup_Value) |
Returns a value from Lookup_Table
based on the input value Lookup_Value. If a lookup
value falls inbetween the actual lookup values in the table, the
next value in the list is selected and its return value
returned. |
Assume following lookup table called LT:| Lookup value | Return value | | 1 | 10 | | 2 | 20 | | 3 | 30 | | 4 | 40 | | 5 | 50 |
VLOOKUP("LT",2) = 20
VLOOKUP("LT",2.5) = 30 |
| YEAR |
YEAR(DateField) |
Converts a julian date (DateField,
e.g. DTS start or finish dates) to a year |
YEAR([Start]) = 2018 (if the task starts in 2018) |
Typically the parameters, e.g. Number, DateField, Text etc., shown in
the Usage column above are meant to be replaced with an
DTS field, which is normally enclosed in square brackets [ ]. The
examples above use specific numbers and text to illustrate the concept.
Note that functions which return a text value would typically
be used in text field formulas. If used in a production field formula,
the end result of the function will depend on the return value,
specifically if this can be converted to a number or not. Similarly
functions which return a numeric value would typically be used in
production field formulas.
Consider the following text field formulas that
illustrate potentially unexpected results, especially when using the "+"
operator:
TEXT(10 + 5, "") + 50: this formula does the
expected addition of 10+5 to get 15, and since at this point the result
can be converted to a number, you can add 50 to get 65.
TEXT(10 + 5, ") + "50" this formula
does the expected addition of 10+5 to get 15, but since it then "adds"
the text "50", the plus symbol performs a concatenation as is usual in
text, and the result is 1550. The result is a text value if used in
another formula, but can implicitly be converted to a number (unlike
e.g. "1550a").
It is good practice however to use the functions
TEXT and VALUE to specifically convert an expression to the expected
type. Excel allows you to use + (add) and &
(concatenate) operators and seems to use these to distinguish between
text and number. DTS can also use both, but instead generally uses the
term behind the operator to determine if it is meant to add or
concatenate so it's best to steer away from using "&" in DTS.
You can enter the name of the field, if you know it,
but if you add the field to the expression by double-clicking the name,
you make sure that the name of the field is correct.