 Text
 Date/Time
 Math
 System
 Conditional
|
Functions and Formulas|
Text
Date/Time
Math
System
Conditional
|
 |
>>> |  |
 |
>>> |  |
| *Tip: See Microsoft Excel help for another reference and even more examples of formulas and expressions. vTask features were designed to be similar to Excel's industry-standard implementation. |
| *Tip:
Functions can be nested. This allows powerful calculations and text manipulation. The example below returns "Total: $130 Dollars"
|
Text Functions
| *Tip:
Text must always be enclosed inside quotes. vTask allows both double quotes and single quotes: =LEN("abc")+LEN('abc')
[Result: 6 ]
If a user variable is used, and it already contains an embedded quote, you can use single quotes or double quotes as parameter delimiters in vTask to distinguish the string (like Javascript). So, if the text string is ab"cd, then =LEN( 'ab"cd' ) will work as expected. Likewise, if the string is ab'cd, then =LEN( "ab'cd" ) will work as expected. |
| Function | Description | Example | Result | |
| & | Text Concatenation: joins multiple text strings into a single string.
|
="A" & "b" | "Ab" | |
| CHAR(value) | Returns the character that is specified for an ASCII code number. | =CHAR(65) | "A" | |
| CHOP(source_text, chop_at_text) | Truncates (shortens) a text string at the first occurrence of the second string.
|
=CHOP("Abcde", "cd") | "Ab" | |
| CHOOSE(index, source_text, delimiter) | Retrieves a substring (item) from a list of values.
|
=CHOOSE(2, "A;B;C", ";") | "B" | |
| CODE(text) | Returns the ASCII code for the first character in a text string. | =CODE("Abc") | 65 | |
| COUNTIF(source_text, find_text) | Counts the number of occurrences of a text string. (Note: the Excel wildcards "*" and "?" are not supported, since they are specific to spreadsheet data).
|
=COUNTIF("AbcAbc", "bc") | 2 | |
| FIND(find_text, source_text) | Searches a source text for a smaller text (case-sensitive), and returns the starting position of the location (or 0 if it is not found). FIND is similar to SEARCH, except that FIND is case sensitive. | =FIND("bc", "Abc") | 2 | |
| LEFT(text, num_chars) | Returns characters from the beginning of a text string. | =LEFT("Abc", 2) | "Ab" | |
| LEN(value) | Returns the number of characters (length) in a text string or numeric value.
NOTE: When using a variable such as %foo, it's best to enclose the variable in quotes: LEN("%foo"). This is because vTask uses loosely-typed variables (the data can change between numeric and text).
|
=LEN("Abc") | 3 | |
| LOWER(text) | Converts all letters in a text string to lowercase. | =LOWER("Abc") | "abc" | |
| LTRIM(source_text, letters) | Chops whitespace (or other characters) from the beginning of a string. LTRIM can strip both the entire string and individual characters. | =LTRIM("Abc", "A \r\n") =LTRIM("Abc", "Ab") |
"bc" "c" |
|
| MID(text, start_position, num_chars) | Returns characters from the middle of a text string, given a starting position and length.
|
=MID("Abcde", 2, 3) | "bcd" | |
| PAD(text, length, pad_string) | Append a text string to another string, until the desired length is reached. If the value of length is negative, or less than the length of the input string, no padding takes place. | =PAD("ABC", 6, "yz") | "ABCyzy" | |
| PROPER(text) | Capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter. Converts all other letters to lowercase letters. | =PROPER("ab CD") | "Ab Cd" | |
| REPLACE(old_text, start_num, num_chars, new_text) | REPLACE replaces part of a text string, based on the number of characters you specify, with a different text string.
|
=REPLACE("Abcd", 2, 2, "*") | "A*d" | |
| RIGHT(text, num_chars) | Returns characters from the end of a text string. | =RIGHT("Abc", 2) | "bc" | |
| RTRIM(source_text, letters) | Chops whitespace (or other characters) from the end of a string. RTRIM can strip both the entire string and individual characters. | =RTRIM("Abc", "c \r\n") =RTRIM("Abc", "bc") |
"Abc" "A" |
|
| SEARCH(find_text, source_text) | Searches a source text for a smaller text, and returns the starting position of the location (or 0 if it is not found). SEARCH is similar to FIND, except that FIND is case sensitive. | =SEARCH("bc", "ABC") | 2 | |
| STRREV(text) | Reverse a text string. | =STRREV("Abc") | "cbA" | |
| SUBSTITUTE(text, old_text, new_text) SUBSTITUTEI(text, old_text, new_text) |
Replaces a part of a string (substring) with a new string. "SUBSTITUTEI" is the case-insensitive version (replaces both upper and lower case letters).
|
=SUBSTITUTE("Abc", "bc", "yz") =SUBSTITUTEI("ABC", "bc", "yz") |
"Ayz" "Ayz" |
|
| TEXT(value, format_text) | Converts a text string to its number representation. This is necessary for concatenating numbers and text. | =TEXT(1234.567, "#,##0.00") =TEXT(TIME(1,23,0),"h:mm") |
"1,234.57" "1:23" |
|
| TRIM(text) | Removes all beginning and trailing spaces from a string of text. (See LTRIM and RTRIM for stripping alternative text.) | =TRIM(" Abc ") | "Abc" | |
| TRIMWHITE(text) | Removes all beginning and trailing whitespace from a string of text. "Whitespace" is any space, tab, or newline. (See LTRIM and RTRIM for stripping alternative text.) | =TRIMWHITE(" Abc ") | "Abc" | |
| UPPER(text) | Converts all letters in a text string to uppercase. | =UPPER("Abc") | "ABC" | |
| VALUE(text) | Converts a text string into its number representation. | =VALUE("123") | 123 |
Date and Time Functions
| *Tip:
As in Excel, you can do date/time addition anywhere when using serial dates. For example: =DATE(2009-1, 12+50, 31-10)
|
| Function | Description | Example | Result |
| DATE(year, month, day) | Returns the serial number that represents a particular date. This number can be used in other date calculations. | =DATE(2009, 8, 22) | 40047 |
| DATEVALUE(date_text) | Returns the serial number of the date represented by date_text. Use DATEVALUE to convert a date represented by text to a serial number. | =DATEVALUE("8/22/2009") =DATEVALUE("5-JUL") |
40047 39999 |
| DAY(serial_date) | Returns the day of a date, represented by a serial number. The day is given as an integer ranging from 1 to 31. | =DAY(DATE(2009,8,22)) | 22 |
| DAYOFYEAR(serial_date) | Returns the day of the year. The day is given as an integer ranging from 1 to 365 (366 if a leap year). | =DAYOFYEAR(DATE(2009,1,1)) | 1 |
| DAYSINMONTH(serial_date) | Returns the total number days in a given month (28-31). If an invalid date is given, the return is 0. | =DAYSINMONTH(DATE(2009,1,1)) | 31 |
| DAYSINYEAR(serial_date) | Returns the total number days in a given year (365 or 366). If an invalid date is given, the return is 365. | =DAYSINYEAR(DATE(2009,1,1)) | 365 |
| HOUR(serial_date) | Returns the hour of a time value. The hour is given as an integer, ranging from 0 (12:00 A.M.) to 23 (11:00 P.M.). | =HOUR(TIME(23,45,59)) | 23 |
| MINUTE(serial_date) | Returns the minutes of a time value. The minute is given as an integer, ranging from 0 to 59. | =MINUTE(TIME(23,45,59)) | 45 |
| MONTH(serial_date) | Returns the month of a date represented by a serial number. The month is given as an integer, ranging from 1 (January) to 12 (December). | =MONTH(DATE(2009,8,22)) | 8 |
| NOW() | Returns the serial number of the current date and time. | =NOW() =YEAR(NOW()) |
39762.64386 2009 |
| SECOND(serial_date) | Returns the seconds of a time value. The second is given as an integer in the range 0 (zero) to 59. | =SECOND(TIME(23,45,59)) | 59 |
| TIME(hour, minute, second) | Returns the serial decimal number for a particular time. This number can be used in other time calculations. | =TIME(12, 0, 0) | 0.5 |
| TIMEVALUE(time_text) | Returns the decimal number of the time represented by a text string. The decimal number is a value ranging from 0 (zero) to 0.99999999, representing the times from 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 P.M.). | =TIMEVALUE("6:35 AM") =TIMEVALUE("1/1/09 13:59") |
0.274305556 0.582638889 |
| TODAY() | Returns the serial number of the current date. | =TODAY() | 39762 |
| WEEKDAY(serial_date) | Returns the day of the week corresponding to a date. The day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday) | =WEEKDAY(DATE(2009,8,22)) | 7 (Saturday) |
| YEAR(serial_date) | Returns the year corresponding to a date. The year is returned as an integer in the range 1900-9999. | =YEAR(DATE(2009,8,22)) | 2009 |
| DATE(2009, 8-6, 22+1) | Illustrates how to do Date Addition and Subtraction. These calculations can be done inline, on each part of the date. | =DATE(2009, 8-6, 22+1) | 23-FEB-2009 |
| TIME(23, 30-10, 59+1) | Illustrates how to do Time Addition and Subtraction. These calculations can be done inline, on each part of the time. | =TIME(23, 30-10, 59+1) | 23:21:00 |
| TEXT(NOW(), "dddd") | Illustrates how to do Date formatting. | =TEXT(NOW(),"dddd") | "Monday" |
| TEXT(NOW(), "hh:mm:ss") | Illustrates how to do Time formatting. | =TEXT(NOW(),"hh:mm:ss") | "13:30:59" |
>>> 
Math Functions
| *Tip:
Math expressions can contain user variables. For example:
|
| *Tip:
Be sure to use parenthesis in your math expressions. This helps to avoid unintended results due to parsing and order-of-precedence issues.
For example, 1+2*3 = 7, however (1+2)*3 = 9
|
| Function | Description | Example | Result |
| ABS(number) | Returns the absolute value of a number (without its sign). | =ABS(-2) | 2 |
| ATAN(number) | Returns the arctangent of a value. | =ATAN(1) | 0.785398 |
| CEILING(number) | Returns a value representing the smallest integer that is greater than or equal to a value. | =CEILING(2.5) | 3 |
| COS(number) | Return the cosine of a value. | =COS(1.047) | 0.500171 |
| DEC2HEX(number) | Converts a decimal number (base 10) to hexadecimal (base 16). Due to the nature of hexadecimal numbers (they can contain the letters A-F), the return will be in text. | =DEC2HEX(26) | "1A" |
| DECIMAL(number) | Returns the decimal (fractional) portion of a number. | =DEC(123.456) | 0.456 |
| FLOOR(number) | Returns a floating-point value representing the largest integer that is less than or equal to a value. | =FLOOR(2.5) | 2 |
| GCD(value1, value2) | Returns the Greatest Common Divisor of the two given numbers. | =GCD(10, 15) | 5 |
| HEX2DEC(hex_number) | Converts a hexadecimal number (base 16) to decimal (base 10). | =HEX2DEC("1A") | 26 |
| INT(number) | Rounds a number down to the nearest integer. | =INT(8.9) | 8 |
| ISODD(number) | Returns TRUE (1) if number is odd, or FALSE (0) if number is even. | =ISODD(3) | 1 |
| LCM(value1, value2) | Returns the Least Common Multiple of the two given numbers. | =LCM(10, 15) | 30 |
| MAX(value1, value2) | Compares two values and returns the value of the larger one. | =MAX(2,3) | 3 |
| MIN(value1, value2) | Compares two values and returns the value of the smaller one. | =MIN(2,3) | 2 |
| MOD(value1, value2) | Returns the remainder after number is divided by divisor. The result sign matches the output from calculator and the standard mod( ) function. | =MOD(2,3) | 2 |
| NUM2WORDS(number) | Converts a numeric value into its English text representation. | =NUM2WORDS(21) | Twenty One |
| PI() | Returns the mathematical constant pi, accurate to 15 digits. | =PI() | 3.14... |
| QUOTIENT(numerator, denominator) | Returns the integer portion of a division. This allows you to discard the remainder portion of a division. | =QUOTIENT(5, 2) | 2 |
| RAND() | Returns a pseudo-random number between 0 and 1. To generate a random number between A and B, use: =RAND()*(B-A)+A (or use "RANDBETWEEN") | =RAND() | 0.50134 |
| RANDBETWEEN(bottom, top) | Returns a pseudo-random integer number between the numbers you specify. | =RANDBETWEEN(1, 100) | 35 |
| ROUND(number, num_digits) | Returns a number rounded to a specified number of decimal places. | =ROUND(2.15, 1) | 2.2 |
| SIN(number) | Returns the sine of a value. | =SIN(30 * PI() / 180) | 0.5 |
| SQRT(number) | Returns the square-root of a value. | =SQRT(16) | 4 |
| TAN(number) | Returns the tangent of a value. | =TAN(0.785) | 0.99920 |
| TEXT(number, format_text) | Converts a text string to its number representation. This is necessary for concatenating numbers and text. | =TEXT(1234.567, "#,##0.00") =TEXT(TIME(1,23,0),"h:mm") |
"1,234.57" "1:23" |
| TRUNC(number, num_digits) | Truncates a number to an integer by removing the fractional (decimal) part of the number. | =TRUNC(8.9, 0) | 8 |
| TYPE(text OR number) | Returns an integer representing the data type of a value: Number = 1, Text = 2. This function is useful for determining how vTask is internally evaluating a parameter. | =TYPE("Abc") | 2 |
| VALUE(text) | Converts a text string into its number representation. | =VALUE("123") | 123 |
| Function | Description | Example | Result |
| + | Addition | =2+3 | 5 |
| - | Subtraction | =2-3 | -1 |
| * | Multiplication | =2*3 | 6 |
| / | Division | =2/3 | 0.66... |
| ^ | Exponential (Power of) | =2^3 | 8 |
| == | Equality Check | = 2==3 ="Ab"=="Ab" |
0 (False) 1 (True) |
| != or <> |
Inequality | =2!=3 =2<>3 ="Ab"<>"Bc" |
1 (True) 1 (True) 0 (False) |
| < | Less Than | =2<3 ="Ab"<"Bc" |
1 (True) 1 (True) |
| <= | Less Than or Equal | =2<=3 | 1 (True) |
| > | Greater Than | =2>3 | 0 (False) |
| >= | Greater Than or Equal | =2>=3 | 0 (False) |
| & | Bitwise AND | =3&6 | 2 |
| | | Bitwise OR | =2|4 | 6 |
| ~ | Bitwise XOR | =2~3 | 1 |
| ! | Logical NOT | =!2 | 0 (False) |
| ++ | Increment | =2++ | 3 |
| -- | Decrement | =2-- | 1 |
| || | Logical OR Checks | =1<2 || 3>4 | 1 (True) |
| && | Logical AND Checks | =1<2 && 3<4 | 1 (True) |
| ( ) | Parenthesis Precedence | =((1+2)*3) =1+2*3 |
9 7 |
System Functions
| *Tip:
Although vTask includes these System functions, a script should normally use the standard File, System and Window commands,
which have a far greater number of options.
However, these functions are useful for circumstances where multiple jobs can be combined together. For example, this formula launches a document if it exists, otherwise it activates another program: |
| Function | Description | Example | Result | |||||||||||||||||||||||||
| FILEEXIST(filename) | Checks for the existence of a file. Returns 1 (TRUE) if the file exists, or else 0 (FALSE) if it does not. The file name can contain wildcards (*, ?) | =FILEEXIST("C:\boot.ini") | 1 | |||||||||||||||||||||||||
| FILESIZE(filename) | Returns the length in bytes of a file. Returns 0 if the file does not exists. | =FILESIZE("C:\Windows\regedit.exe") | 146432 | |||||||||||||||||||||||||
| FILENAMECLEAN(filename) | Removes invalid characters from a file name. It also strips whitespace from the front and end of the name.
If vTask determines that a full path is included, the "/" and "\" characters will be allowed.
This function uses the Windows file naming rules:  |
=FILENAMECLEAN("Report: December?") | "Report December" | |||||||||||||||||||||||||
| FOLDEREXIST(filename) | Checks for the existence of a directory. Returns 1 (TRUE) if the folder exists, or else 0 (FALSE) if it does not. | =FOLDEREXIST("C:\Program Files") | 1 | |||||||||||||||||||||||||
| CLOSEWINDOW(handle) | Closes a window, and ends the program associated with it if the window was the main application window. | =CLOSEWINDOW(FINDWINDOW("Notepad")) | 1 | |||||||||||||||||||||||||
| FINDWINDOW("title") | Searches for a window that matches the given title. It first checks for exact matches, then window titles that start with the text, and finally checks with a case-insensitive partial title match. If found, the window handle is returned. This window handle (hwnd) can be used in other window functions. | =FINDWINDOW("Calculator") | 451023 | |||||||||||||||||||||||||
| ISWINDOW(handle) | Searches for a window based on its window handle. If found, the return is 1, else it is 0. This window handle (hwnd) is a number which can be obtained by the "FINDWINDOW" functions. | =ISWINDOW(451023) | 1 | |||||||||||||||||||||||||
| SETFOCUS(handle) | Activates the designated window. This implementation of SETFOCUS will also activate minimized windows and other applications. This window handle (hwnd) can be used in other window functions. | =SETFOCUS(FINDWINDOW("Calculator")) | 1 | |||||||||||||||||||||||||
| GETPIXEL(x, y) | Retrieves the red, green, blue (RGB) color value of the pixel at the specified coordinates.
The X (horizontal) and Y (vertical) parameters are 0-based. Therefore, coordinate (0,0) is the upper left corner of the screen.
The return value is a string in hexadecimal RGB format (the same as used in HTML web pages). "#FFFFFF" is white, "#0000FF" is blue, and "#000000" is black. |
=GETPIXEL(100, 200) | #004E98 | |||||||||||||||||||||||||
| GETTICKCOUNT( ) | Retrieves the number of milliseconds that have elapsed since Windows was started. This is useful for timing and delays. | =GETTICKCOUNT() | 14161046 | |||||||||||||||||||||||||
| INFO(type_text) | Returns information about the current operating environment.
This function is similar to the INFO() function in Excel.
Available options are:
|
=INFO("osname") =INFO("osversionex") =INFO("screenx") |
"Windows XP" "6.1.7100" 1600 |
|||||||||||||||||||||||||
| ISKEYPRESSED( key ) | Determines if the specified key is currently pressed (1 if pressed, 0 if not). The key names correspond to the list in the "Keyboard System Keys" menu. A typical keyname is {CapsLock}, or {A}. | =ISKEYPRESSED( {NumLock} ) | 1 | |||||||||||||||||||||||||
| RUN("program") | Starts a program, launches a document, or loads a website. | =RUN("notepad.exe") =RUN("www.vtaskstudio.com") |
1 | |||||||||||||||||||||||||
Conditional Functions
| *Tip:
Functions can be nested. This allows powerful calculations and text manipulation. The example below returns "Total: $130 Dollars"
|
| Function | Description | Example | Result |
| IF(test, if_true, if_false) | Returns one value if a condition you specify evaluates to TRUE (1) and another value if it evaluates to FALSE (0). | =IF(1>2, 8, 9) | 9 |
| IF(test, "if_true", "if_false") | IF can also compare text strings, and return text strings. | =IF("Ab"=="Ab", "Yes", "No") | "Yes" |
| IF( (test-1 || test-2), if_true, if_false) | Allows you to perform multiple OR conditionals. If any of the tests pass, then it evaluates to TRUE. | =IF((1>2 || 2<3 || 3==4), 8, 9) | 8 |
| IF( (test-1 && test-2), if_true, if_false) |
Allows you to perform multiple AND conditionals. If all of the tests pass, then it evaluates to TRUE. | =IF((1<2 && 2<3 && 3==3), 8, 9) | 8 |
| IF( (test-1 && test-2) || test-3, if_true, if_false) |
An example multiple OR and AND conditionals. Be sure to enclose each test in parenthesis to ensure correct logical evaluation. | =IF(1>2 || (2<3 && 3==4), 8, 9) | 9 |
| =(1<2) | Conditionals can be done without the IF statement, and they then become "Logical Boolean Comparisons." Boolean comparisons can only return either 1 (true) or 0 (false). | =(1<2) =(1>2 || 3==4) |
1 0 |
| *Tip -- Advanced IF( ) Example:
While there are only 3 parameters of the IF() function (also called "Ternary conditional"), there's nothing to prevent you from adding more than 2 outcomes. You can keep adding additional ...ELSE, ...ELSE, ...ELSE options by embedding more IF() functions. For example, the following step will show "Good Morning" if it's before 1pm, else it will show "Good Afternoon" if it's before 5pm, else it will show "Good Evening."  For comparison, if you had to do it with normal steps, it would be much larger (7 steps instead of 1):  The formula might look complex, but it's just adding a new check to each embedded IF( ). In fact, thanks to vTask's compatibility with Microsoft Excel, this works exactly the same if you copy & paste the formula into a spreadsheet (very helpful for testing):  |