Custom equation functions and operators

Aha! custom worksheets and calculation columns support many of the functions and operators you are used to in your preferred spreadsheet application. These functions and operators can be used in equations to calculate numbers and dates. For example, you can track a feature's cost through development or the number of remaining days until the feature launches.

Functions

Operators

Functions

Aha! functions

 Function Description Syntax field Use this function to reference other values in your equation. field("Value 1")

Numerical functions

 Function Description Syntax avg Use this function to present the average value of a data set. avg(1, 2, 3)=> 2 avg(field("field name"), field("field name 2")...) max Use this function to present the maximum value in a data set. max(1, 2, 3)=> 3 max(field("field name"), field("field name 2")...) min Use this function to present the minimum value in a data set. min(1, 2, 3)=> 1 min(field("field name"), field("field name 2")...) number Use this function to force a value to be a number. number(value)  number(field("field name") number(1.4) => 1.4 number("13") => 13 number(true) => 1 number(false) => 0

Date functions

 Function Description Syntax date Use this function to force a value to be a date. date(value)  date(field("field name") date(1528693264) =>2018-06-11 date("2018-06" + "-10") => 2018-06-10 date("2018-04-21T01:02:03") => 2018-04-21 datediff Use this function to present the total number of days, weeks, months, or years between dates. Valid units: day week month year datediff("2019-01-01","2019-01-31","day") => 30 datediff("2019-01-01","2019-04-01","week") => 13 dateformat Use this function to present a specific date in a custom format.  Available formats:  %Y - Year with century  %y - Year without century %m - Month of the year, zero-padded  %_m - Month of the year, blank-padded  %-m - Month of the year, no-padded  %B - Month, the full name  %b - The abbreviated month name  %d - Day of the month, zero-padded  %-d - Day of the month, no-padded  %e - Day of the month, blank-padded dateformat("2019-01-01", "%Y") => 2019 dateformat("2019-01-01", "%B-%e,%y") => January 1, 2019 today Use this function to present the current date. Note: The today function is only available in calculation columns. It is not available in custom worksheets. today() => 2019-03-11 day Use this function to present the day of the month for a specific date. day("2019-03-08") => 8 month Use this function to present the month of a specific date. month("2019-03-08") => 3 quarter Use this function to present the quarter of a specific date.   The quarter for a given date is impacted by the Fiscal year start setting in Account > Profile. quarter("2019-03-08") => 1 time Use this function to force a value to be a time. time(value)  time(field("field name") time(1528693264) =>2018-06-11T05:01:04Z time("2018-04-21") => 2018-04-21T00:00:00Z year Use this function to present the year of a specific date. year("2019-03-08") => 2019

Logical functions

 Function Description Syntax boolean Use this function to force a value to be a boolean. boolean(value) boolean("True") => true boolean("False") => false boolean(1) => true boolean(0) => false if Use this function to evaluate a condition and present one value if the condition is true or another value if false. if(condition, true_value, false_value) if(2 > 1, "Green", "Red") => "Green" if(false, 1, -1) => -1

Text functions

 Function Description Syntax string Use this function to force a value to be a string. string(value) string(1.4) => "1.4" string(1243) => "1243" string(true) => "True" string(false) => "False" length Use this function to see the length of a string by character count. length(string) length("Example")=> 7 substr Use this function to extract a  portion of a string, by index and character count. Negative start and count values are not supported. substr(string, start, count) substr("Example", 1, 2)=> "xa" strpos Use this function to find the index of a substring in a string (a string within a string). Returns -1 when no match is found. strpos(string, search) strpos("Example", "amp")=> 2

Operators

Numerical operators

 Operator Description Examples + Use this operator to add two numerical operands, concatenate two strings, or add an offset to a time or date. 12 + 44.5 => 56.5 "2019-03-12" + 10 => '2019-03-22' "first" + " " + "last" => "first last" - Use this operator to subtract two numerical operands or to subtract an offset from a time or date. Note: if you subtract one or more dates from another, the output is in number of days. The datediff function provides a simpler way to do this. 44.5 - 12 => 32.5 "2019-03-12" - 10 => "2019-03-02" "2019-03-12" - "2019-03-02" => "10" * Use this operator to multiply two numerical operands. 44.5 * 11 => 489.5 / Use this operator to divide two numerical operands. 44 / 16 => 2.75

Logical operators

 Operator Description Examples > Use this operator to compare two numerical operands and return true if the first is greater than the second. 1 > 2 => false 2 > 1 => true < Use this operator to compare two numerical operands and return true if the first is less than the second. 1 < 2 => true 2 < 1 => false >= Use this operator to compare two numerical operands and return true if the first is greater than or equal to the second. 2 >= 2 => true 2 >= 1 => true 1 >= 2 => false <= Use this operator to compare two numerical operands and return true if the first is less than or equal to the second. 1 <= 2 => true 2 <= 2 => true 2 <= 1 => false == Use this operator to compare two numerical, boolean, strings, dates, or times and return true if the operands are equal. 1 == 1 => true "lorem" == "ipsum" => false "2018-05-09" == "2018-05-09"=> true true == false => false != Use this operator to compare two numerical, boolean, strings, dates, or times and return true if the operands are not equal. 1 != 1=> false "lorem" != "ipsum" => true "2018-05-09" != "2018-05-09"=> false true != false => true and This operator returns true if both operands are true. true and true => true true and false => true or This operator returns true if either operand is true. true or true => true true or false => true false or false => false not This operator returns true if the operand is false. not true => false not false => true