Totango Field Functions Formulas

# Type Name Description Arguments Syntax Example
1MathABSReturns the absolute value of a number. Negative numbers are converted to positive numbers, positive numbers are unaffected.[number] - The number to calculate the absolute value of.ABS (number)ABS(-25) = 25
2LogicalANDA logical function of more than one condition. AND returns either TRUE or FALSE. True is returned only if all conditions are TRUE[logical_expression1] - An expression or reference to a cell containing an expression that represents a logical value, i.e. "TRUE" or "FALSE", or an expression that can be coerced to a logical value., [logical_expression2]… - [optional] repeatable. More expressions that represent logical values.AND (logical1, [logical2], ...)AND(2>0 , 1<10) = TRUE
3MathAVERAGECalculates the average (arithmetic mean) of the given numbers.[number1] - A number that refers to numeric values., [number2]… - [optional] repeatable. Additional values to consider when calculating the average value.AVERAGE (number1, [number2], ...)AVERAGE(0.5, 2) = 1.25
4MathCEILINGRounds a given number up to the nearest specified multiple.[number] - The number that should be rounded., [significance] - The multiple to use when rounding.CEILING (number, significance)CEILING(10, 3) = 12; CEILING(36, 7) = 42
5TextCHOOSEReturns a value from a list using a given position or index. [index_num] - The value to choose. A number between 1 and 254., [value1] - The first value from which to choose., [value2] - [optional] The second value from which to choose.CHOOSE (index_num, value1, [value2], ...)CHOOSE(2, "red" , "blue" , "green") = "blue""
6TextCLEANTakes a text string and returns text that has been cleaned of line breaks and other non-printable characters.[text] - The text to clean.CLEAN (text)CLEAN("Monthly\n report") = "Monthly report"
7TextCONCATENATEConcatenates joins up to 30 text items together and returns the result as text.[text1] - The first text value to join, [text2] - The second text value to join together., [text3] - [optional] The third text value to join.CONCATENATE (text1, text2, [text3], ...)CONCATENATE(account123, "__", user123) = "account123__user123"
8MathCONSTANTReturn constant value[value1] - The text or number value.CONSTANT (value1)CONSTANT("TEST") = "TEST"; CONSTANT(24.5) = 24.5
9MathCOUNTReturns the count of the values that are numbers.[value1] - An item, cell reference, or range., [value2] - [optional] An item, cell reference, or range.COUNT (value1, [value2], ...)COUNT("not_number", 12) = 1
10MathCOUNTAReturns the count of values that contain numbers, text, logical values, error values, and empty text (""). COUNTA does not count empty cells.[value1] - An item, cell reference, or range., [value2] - [optional] An item, cell reference, or range.COUNTA (value1, [value2], ...)COUNTA(2, 11, "Text") = 3
11DateDATECreates a valid date as a string from individual year, month, and day components.[year] - Number for year., [month] - Number for month., [day] - Number for day.DATE (year, month, day)DATE(65, 11, 2) = 24048
12DateDATEVALUEConvert a date string to a date value.[date_string] - a string representing a date.DATEVALUE(date_string)DATEVALUE(Today()) = 44358
13DateDAYReturns the day of the month as a number between 1 to 31 from a given date.[date] - A valid date as a string.DAY (date)DAY("2020-10-02") = 2
14DateDAYS360Returns the number of days between two dates based on a 360-day year. Calculations based on a 360-day year comes from certain accounting calculations where all 12 months are considered to have 30 days.[start_date] - The start date., [end_date] - The end date., [method] - [optional] The type of day count basis to use. FALSE (default) is US method, TRUE is European method.DAYS360 (start_date, end_date, [method])DAYS360("2019-01-01", "2020-10-02") = 631
15MathDELTAChecks if two numeric values are equal. If values are equal,DELTA returns 1, otherwise, DELTA returns zero. Note: Use the VALUE() function to convert string to number.[number1] - The first number., [number2] - [optional] The second number.DELTA (number1, [number2])DELTA(2, 15) = 0; DELTA(2, 2) = 1
16DateEDATEReturns a date on the same day of the month, n months in the past or future.[start_date] - Start date as a valid Excel date., [months] - Number of months before or after start_date.EDATE (start_date, months)EDATE("2020-10-5", 1) = "2020-11-5";
17DateEOMONTHReturns the last day of the month, n months in the past or future. [start_date] - A date that represents the start date in a valid Excel format., [months] - The number of months before or after start_date.EOMONTH (start_date, months)EOMONTH("2020-10-02", 2) = "2020-12-31"
18TextEXACTTests whether two strings are identical (case sensitive).[text1] - The first text string to compare., [text2] - The second text string to compare.EXACT (text1, text2)EXACT("Account1", "Account2") = FALSE; EXACT("User1", "User1") = TRUE
19LogicalFALSEReturns the value FALSE.FALSE ()FALSE() = FALSE
20TextFINDReturns the position (as a number) of one text string inside another. [find_text] - The text to find., [within_text] - The text to search within., [start_num] - [optional] The starting position in the text to search. Optional, defaults to 1.FIND (find_text, within_text, [start_num])FIND("user1", "account123__user1") = 13
21FormattingFIXEDFormat number with fixed number of decimal places[number] - The number to round and format., [decimals] - [optional] Number of decimals to use. Default is 2., [no_commas] - [optional] Suppress commas. TRUE = no commas, FALSE = commas.FIXED (number, [decimals], [no_commas])FIXED(11, 2) = 11.00
22MathFLOORRounds a given number down to the nearest specified factor.[number] - The number that should be rounded., [significance] - The multiple to use when rounding.FLOOR (number, significance)FLOOR(10, 3) = 9; FLOOR(36, 7) = 35
23DateHOURReturns the hour component of a time as a number between 0-23.[Time] - A valid time number.HOUR (Time)HOUR("11:40:59") = 11
24LogicalIFRuns a logical test and returns one value for a TRUE result, and another for a FALSE result. Note: If the logical test in numeric, use the VALUE() function to convert string to number.[logical_test] - A value or logical expression that can be evaluated as TRUE or FALSE., [value_if_true] - [optional] The value to return when logical_test evaluates to TRUE., [value_if_false] - [optional] The value to return when logical_test evaluates to FALSE.IF (logical_test, [value_if_true], [value_if_false])IF(accountID = "Account123", 1, 2) = 1
25LogicalIFERRORReturns a custom result when a formula generates an error, and a standard result when no error is detected.[value] - The value, reference, or formula to check for an error., [value_if_error] - The value to return if an error is found.IFERROR (value, value_if_error)IFERROR(14/0, "error message") = "error message"
26LogicalIFNULLCheck if value is null and returns one value for a TRUE result, and another for a FALSE result.[value] - A value or logical expression to check if null,, [value_if_true] - The value to return when [value] evaluates to TRUE., [value_if_false] - The value to return when [value] evaluates to FALSE.IFNULL (value, value_if_true, value_if_false)IFNULL(null, "null value", "non-null value") = "null value"; IFNULL("test", "null value", "non-null value") = "non-null value"
27LogicalISBLANKReturns TRUE when the value is empty, and FALSE when a value is not empty.[value] - The value to check.ISBLANK (value)ISBLANK(11.2) = FALSE
28LogicalISERRReturns TRUE for any error type except the #N/A error.[value] - The value to check for any error but #N/A.ISERR (value)ISERR(12/0) = TRUE
29LogicalISEVENReturns TRUE when a numeric value is even, and FALSE for odd numbers. Note: Use the VALUE() function to convert string to number.[value] - The numeric value to check.ISEVEN (value)ISEVEN(10) = TRUE; ISEVEN(11) = FALSE
30LogicalISLOGICALReturns TRUE when a value contains the logical values TRUE or FALSE, and returns FALSE for values that contain any other value, including empty value.[value] - The value to test as logical.ISLOGICAL (value)ISLOGICAL(TRUE()) = TRUE
31LogicalISNAReturns TRUE when a value contains the #N/A error and FALSE for any other value, or any other error type.[value] - The value to check if #N/A.ISNA (value)ISNA("TEST") = FALSE
32LogicalISNONTEXTReturns TRUE for any non-text value, for example, a number, a date, a time, etc.[value] - The value to check.ISNONTEXT (value)ISNONTEXT(2) = TRUE
33LogicalISNULLReturns TRUE when a value is null, otherwise return FALSE.[value] - The value to check if null.ISNULL (value)ISNULL(null) = TRUE; ISNULL("test") = FALES
34LogicalISNUMBERReturns TRUE when a value is a number, and FALSE if not.[value] - The value to check.ISNUMBER (value)ISNUMBER("string-value") = FALSE
35LogicalISODDReturns TRUE when a numeric value is odd, and FALSE for even numbers.[value] - The numeric value to check.ISODD (value)ISODD(2) = FALSE; ISODD(11) = TRUE
36LogicalISTEXTReturns TRUE when a cell contains a text, and FALSE if not.[value] - The value to check.ISTEXT (value)ISTEXT("string-value") = TRUE
37TextLEFTExtracts a given number of characters from the left side of a supplied text string.[text] - The text from which to extract characters., [num_chars] - [optional] The number of characters to extract, starting on the left side of text.LEFT (text, [num_chars])LEFT("Account123", 3) = "Acc"
38TextLENReturns the length of a given text string as the number of characters.[text] - The text for which to calculate length.LEN (text)LEN("string-value") = 12
39TextLOWERReturns a lower-case version of a given text string. Numbers and punctuation are not affected.[text] - The text that should be converted to lowercase.LOWER (text)LOWER("String-Value") = "string-value"
40MathMAXReturns the largest numeric value in a range of values. Note: Use the VALUE() function to convert string to number.[number1] - Number, reference to numeric value, or range that contains numeric values., [number2] - [optional] Number, reference to numeric value, or range that contains numeric values.MAX (number1, [number2], ...)MAX(2, 11) = 11
41MathMAXAReturns the largest numeric value in a range of values. Note: Use the VALUE() function to convert string to number[value1] - Number, reference to numeric value, or range that contains numeric values., [value2] - [optional] Number, reference to numeric value, or range that contains numeric values.MAXA (value1, [value2], ...)MAXA(2, 11, TRUE()) = 11
42TextMIDExtracts a given number of characters from the middle of a supplied text string.[text] - The text to extract from., [start_num] - The location of the first character to extract., [num_chars] - The number of characters to extract.MID (text, start_num, num_chars)MID("Account123", 2, 3) = "cco"
43MathMINReturns the smallest numeric value in a range of values. Note: Use the VALUE() function to convert string to number.[number1] - Number, reference to numeric value, or range that contains numeric values., [number2] - [optional] Number, reference to numeric value, or range that contains numeric values.MIN (number1, [number2], ...)MIN(2, 11, 65, ) = 2
44MathMINAReturns the smallest numeric value in a range of values. Note: Use the VALUE() function to convert string to number.[value1] - Number, reference to numeric value, or range that contains numeric values., [value2] - [optional] Number, reference to numeric value, or range that contains numeric values.MINA (value1, [value2], ...)MINA(2, 11, 65, ) = 2; MINA(2, 11, 65, TRUE()) = 1
45DateMINUTEExtracts the minute component of a time as a number between 0-59.[time] - A valid date or time.MINUTE (time)MINUTE("11:40:59") = 40
46DateMONTHExtracts the month from a given date as number between 1 to 12.MONTH("2020-10-2") = 10, [date] - A valid Excel date.MONTH (date)MONTH (date)
47DateNETWORKDAYSCalculates the number of working days between two dates.[start_date] - The start date as serial number., [end_date] - The end date as serial number.NETWORKDAYS (start_date, end_date)NETWORKDAYS("2019-01-01", "2020-10-02") = 459;
48LogicalNOTReturns the opposite of a given logical or boolean value. When given TRUE, NOT returns FALSE.When given FALSE, NOT returns TRUE.[logical] - A value or logical expression that can be evaluated as TRUE or FALSE.NOT (logical)NOT(TRUE()) = FALSE NOT(FALSE()) = TRUE
49DateNOWReturns the current date as a string.NOW ()NOW() = "2020-10-02"
50MathODDReturns the next odd integer after rounding a given number up.[number] - The number to round up to an odd integer.ODD (number)ODD(9.1) = 11; ODD(-2.1) = -3
51LogicalORLogical function to test multiple conditions. OR returns either TRUE if at least one condition is true, or FALSE if all conditions are FALSE.[logical1] - The first condition or logical value to evaluate., [logical2] - [optional] The second condition or logical value to evaluate.OR (logical1, [logical2], ...)OR(TRUE(), FALSE()) = TRUE; OR(FALSE(), FALSE()) = FALSE
52TextPROPERCapitalizes words given text string.[text] - The text that should be converted to proper case.PROPER (text)PROPER("All renewals are secured") = "All Renewals Are Secured""
53TextREPLACEReplaces characters specified by location in a given text string with another text string. [old_text] - The text to replace., [start_num] - The starting location in the text to search., [num_chars] - The number of characters to replace., [new_text] - The text to replace old_text with.REPLACE (old_text, start_num, num_chars, new_text)REPLACE("Account123", 8, 3, 456) = "Account456""
54TextRIGHTExtracts a given number of characters from the right side of a supplied text string.[text] - The text from which to extract characters on the right., [num_chars] - [optional] The number of characters to extract, starting on the right. Optional, default = 1.RIGHT (text, [num_chars])RIGHT("Account123",3)="123"
55MathROUNDReturns a number rounded to a given number of digits.[number] - The number to round., [num_digits] - The number of digits to which number should be rounded.ROUND (number, num_digits)ROUND(5.7845, 1) = 5.8; ROUND(5.7845, 2) = 5.78; ROUND(5.7845, 3) = 5.785
56MathROUNDDOWNReturns a number rounded down to a given number of places.[number] - The number to round down., [num_digits] - The number of digits to which number should be rounded down.ROUNDDOWN (number, num_digits)ROUNDDOWN(5.7899, 1) = 5.7; ROUNDDOWN(5.7899, 2) = 5.78; ROUNDDOWN(5.7899, 3) = 5.789
57MathROUNDUPReturns a number rounded up to a given number of decimal places[number] - The number to round up., [num_digits] - The number of digits to which number should be rounded up.ROUNDUP (number, num_digits)ROUNDUP(5.1242, 1) = 5.2; ROUNDUP(5.1242, 1) = 5.2; ROUNDUP(5.1242, 3) = 5.125
58TextSEARCHReturns the location of one text string inside another.[find_text] - The text to find., [within_text] - The text to search within., [start_num] - [optional] Starting position in the text to search. Optional, defaults to 1.SEARCH (find_text, within_text, [start_num])SEARCH("renew", "All renewals are secured")=5
59DateSECONDReturns the second component of a time as a number between 0-59.[time] - A valid time in a format Excel recognizes.SECOND (time)SECOND("9:10:15") = 15
60FormattingSIGNReturns the sign of a number as +1, -1 or 0. If number is positive, SIGN returns 1. If number is negative, sign returns -1. If number is zero, SIGN returns 0.[number] - The number to get the sign of.SIGN (number)SIGN(2) =1; SIGN(0) = 0; SIGN(-25) = -1
61TextSUBSTITUTEReplaces text in a given string by matching.[text] - The text to change., [old_text] - The text to replace., [new_text] - The text to replace with., [instance] - [optional] The instance to replace. If not supplied, all instances are replaced.SUBSTITUTE (text, old_text, new_text, [instance])SUBSTITUTE("952-455-7865", "-", " ") = "952 455 7865"
62MathSUBTOTALSUBTOTAL function returns an aggregate result for supplied values. SUBTOTAL can return a SUM, AVERAGE, COUNT, MAX, and others.[function_num] - A number that specifies which function to use in calculating subtotals within a list: 1 - AVERAGE, 2 - COUNT, 3 - COUNTA, 4 - MAX, 5 - MIN, 6 - PRODUCT, 7 - STDEV, 9 - SUM, [ref1] - A named range or reference to subtotal., [ref2] - [optional] A named range or reference to subtotal.SUBTOTAL (function_num, ref1, [ref2], ...)SUBTOTAL(1, 2, 11) = 6.5; SUBTOTAL(4, 2, 11) = 11
63MathSUMReturns the sum of values supplied. Note: Use the VALUE() function to convert string to number.[number1] - The first value to sum., [number2] - [optional] The second value to sum., [number3] - [optional] The third value to sum.SUM (number1, [number2], [number3], ...)SUM(2,11) = 13
64MathSUBTRACTReturns the subtraction of values supplied.[number1] - The first value to subtract., [number2] - The second value to sum.SUBTRACT (number1, number2)SUBTRACT(8,2) = 6
65MathMULTIPLYReturns the multiplication of values supplied.[number1] - The first value to multiply., [number2] - The second value to multiply.MULTIPLY (number1, number2)MULTIPLY(8,2) = 16
66MathDIVIDEReturns the division of values supplied.[number1] - The first value to divide., [number2] - The second value to divide.DIVIDE (number1, number2)DIVIDE(8,2) = 4
67TextTReturns text when given a text value and an empty string ("") for numbers, dates, and the logical values TRUE and FALSE.[value] - The value to return as text.T (value)T("string-value") = "string-value"; T(TRUE()) = ""
68FormattingTEXTReturns a number in a given number format, as text.[value] - The number to convert., [format_text] - The number format to use.TEXT (value, format_text)TEXT(0.5, $0.00) = $0.50; TEXT(44106, m/d/yyyy) = 10/2/2020
69DateTIMEBuilt-in function that allows you to create a time with individual hour, minute, and second components.[hour] - The hour for the time you wish to create., [minute] - The minute for the time you wish to create., [second] - The second for the time you wish to create.TIME (hour, minute, second)TIME(2, 11, 2) = 0.0909953703703704
70DateTODAYReturns the current date as a stringTODAY ()TODAY() = "2020-10-02"
71TextTRIMStrips extra spaces from text, leaving only a single space between words and no space characters at the start or end of the text.[text] - The text from which to remove extra space.TRIM (text)TRIM(" string-with-space ") = "string-with-space"
72LogicalTRUEReturns the logical value TRUE.TRUE()TRUE() = TRUE
73FormattingTRUNCReturns a truncated number based on an (optional) number of digits.[number] - The number to truncate., [num_digits] - [optional] The precision of the truncation (default is 0).TRUNC (number, [num_digits])TRUNC(4.9) = 4; TRUNC(-3.51, 1) = -3.5
74TextUPPERReturns a upper-case version of a given text string.[text] - The text thatto convert to upper case.UPPER (text)UPPER("string-value") = "STRING_VALUE"
75FormattingVALUEConverts text that appears in a recognized format (i.e. a number, date, or time format) into a numeric value.[text] - The text value to convert to a number.VALUE (text)VALUE("0.5") = 0.5
76DateWEEKDAYTakes a date and returns a number between 1-7 representing the day of week. By default, WEEKDAY returns 1 for Sunday and 7 for Saturday.[time] - The date for which you want to get the day of week., [return_type] - [optional] A number representing day of week mapping scheme. Default is 1.WEEKDAY (time, [return_type])WEEKDAY("2020-10-02") = 6 (Friday);
77DateWEEKNUMTakes a date and returns a week number (1-54) that corresponds to the week of year. The WEEKNUM function starts counting with the week that contains January 1. By default, weeks begin on Sunday.[time] - A valid Excel date format., [return_type] - [optional] The day the week begins. Default is 1.WEEKNUM (time, [return_type])WEEKNUM("2020-10-02", 1) = 40;
78DateWORKDAYTakes a date and returns the nearest working day in the future or past, based on an offset value you provide.[start_date] - The date from which to start., [days] - The working days before or after start_date., [holidays] - [optional] A list dates that should be considered non-work days.WORKDAY (start_date, days, [holidays])WORKDAY("2020-10-02", 2) = "2020-10-06"
79DateYEARReturns the year component of a date as a 4-digit number.[date] - A valid date.YEAR (date)YEAR("2020-10-02") = 2020
80DateYEARFRACReturns a decimal value that represents fractional years between two dates.[start_date] - The start date., [end_date] - The end date., [basis] - [optional] The type of day count basis to use (see below).YEARFRAC (start_date, end_date, [basis])YEARFRAC("2019-01-01", "2020-10-02") = 1.75277777777778