Functions
Functions are used in formulae to establish values. The following functions are 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:
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:
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.
Examples
-
UPPER("abc")used in a production field formula returns #VALUE! -
UPPER("3.14")used in a production field returns 3.14 -
VALUE("abc")used in a production field formula returns #VALUE! -
VALUE("3.14")used in a production field returns 3.14
Consider the following text field formulas that illustrate potentially unexpected results, especially when using the "+" operator:
TEXT(10 + 5, "") + 50 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" 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.
Note: If your Windows locale is set such that the decimal separator is a comma, then the function parameters are separated by a semi-colon (;), otherwise they are separated by a comma.
Related topics and activities
