Functions and Operators for Calculated Field Formulas

Archer provides a library of functions and operators that you can use to build a formula.

On this page

Date functions

The following table provides a list of date functions. Use these functions to produce dynamic values and to manipulate date information. For more details on each individual function, see Date Functions.

  • DATEADD Function
  • DATEDIF Function
  • DATEFORMAT Function
  • DATETIMEVALUE Function
  • DAY Function
  • HOUR Function
  • MINUTE Function
  • MONTH Function
  • MONTHNAME Function
  • NOW Function
  • QUARTER Function
  • TODAY Function
  • WEEKDAY Function
  • WEEKNUMBER Function
  • YEAR Function

Important: Dates and times are converted to Coordinated Universal Time (UTC) in Archer database. As a result, dates and times in calculations are returned in UTC.

Date format descriptions

The following table describes date elements.

Date Element

Return Example

M

Displays the month as a number without a leading zero (Example: 1)

MM

Displays the month as a number with a leading zero (Example: 01)

MMMM

Displays the month as a full month name (Example: January)

d

Displays the day as a number without a leading zero (Example: 5)

dd

Displays the day as a number with a leading zero (Example: 05)

dddd

Displays the day as a full name (Example: Monday)

yy

Displays the year as a 2-digit number (Example: 06)

yyyy

Displays the year as a 4-digit number (Example: 2006)

h

Displays the hour as a 1-digit or 2-digit number based on a 12-hour clock format (Example: 9)

hh

Displays the hour as a 2 digit number (with a leading a leading zero, if necessary) based on a 12-hour clock format (Example: 09)

H

Displays the hour as a 1-digit or 2-digit number based on a 24-hour clock format (Example: 13)

HH

Displays the hour as a 2-digit number based on a 24-hour clock format (Example: 13)

m

Displays the minute as a number without leading zeros (Example: 5)

mm

Displays the minute as a number with leading zeros (Example: 05)

t

Displays the 1-letter AM/PM designator appropriate for the given time, regardless of whether the time is based on a 12-hour or 24-hour clock. (Example: 1:00 P for 12-hour clock; 13:00 P for 24-hour clock)

tt

Displays the 2-letter AM/PM designator appropriate for the given time, regardless of whether the time is based on a 12-hour or 24-hour clock. (Example: 1:00 PM for 12-hour clock; 13:00 PM for 24-hour clock)

Financial functions

The following table provides a list of financial functions. Use these functions to run common calculations associated with the financial industry. For more details on each individual function, see Financial Functions.

  • DB Function
  • DDB Function
  • FV Function
  • IPMT Function
  • IRR Function
  • ISPMT Function
  • MIRR Function
  • NPER Function
  • NPV Function
  • PMT Function
  • PPMT Function
  • PV Function
  • RATE Function
  • SLN Function
  • SYD Function
  • VDB Function

Logical functions

The following table provides a list of logical functions. Use these functions to evaluate an expression and return a specific result. For more details on each individual function, see Logical Functions.

  • AND Function
  • IF Function
  • NOT Function
  • OR Function

Math functions

The following table provides a list of math functions. Use these functions to manipulate numeric values through a variety of options. For more details on each individual function, see Math Functions.

  • ABS Function
  • ACOS Function
  • ACOSH Function
  • ASIN Function
  • ASINH Function
  • ATAN Function
  • ATAN2 Function
  • ATANH Function
  • CEILING Function
  • COMBIN Function
  • COS Function
  • COSH Function
  • DEGREES Function
  • EVEN Function
  • EXP Function
  • FACT Function
  • FLOOR Function
  • INT Function
  • LN Function
  • LOG Function
  • LOG10 Function
  • MOD Function
  • ODD Function
  • PI Function
  • POWER Function
  • PRODUCT Function
  • QUOTIENT Function
  • RADIANS Function
  • RAND Function
  • ROUND Function
  • ROUNDDOWN Function
  • ROUNDUP Function
  • SIGN Function
  • SIN Function
  • SINH Function
  • SQRT Function
  • SUM Function
  • SUMIF Function
  • SUMPRODUCT Function
  • SUMSQ Function
  • SUMX2MY2 Function
  • SUMX2PY2 Function
  • SUMXMY2 Function
  • TAN Function
  • TANH Function
  • TRUNC Function

Statistics functions

The following table provides a list of statistics functions. For more details on each individual function, see Statistics Functions.

  • AVEDEV Function
  • AVERAGE Function
  • AVERAGEA Function
  • BINOMDIST Function
  • CHIDIST Function
  • CHIINV Function
  • CONFIDENCE Function
  • CORREL Function
  • COUNT Function
  • COUNTA Function
  • COUNTBLANK Function
  • COUNTIF Function
  • COVAR Function
  • CRITBINOM Function
  • DEVSQ Function
  • EXPONDIST Function
  • FDIST Function
  • FINV Function
  • FISHER Function
  • FISHERINV Function
  • FORECAST Function
  • GAMMADIST Function
  • GAMMAINV Function
  • GAMMALN Function
  • GEOMEAN Function
  • HARMEAN Function
  • HYPGEOMDIST Function
  • INTERCEPT Function
  • KURT Function
  • LARGE Function
  • LOGINV Function
  • LOGNORMDIST Function
  • MAX Function
  • MAXA Function
  • MEDIAN Function
  • MIN Function
  • MINA Function
  • MODE Function
  • NEGBINOMDIST Function
  • NORMDIST Function
  • NORMINV Function
  • PEARSON Function
  • PERCENTILE Function
  • PERCENTRANK Function
  • PERMUT Function
  • POISSON Function
  • PROB Function
  • QUARTILE Function
  • RANK Function
  • RSQ Function
  • SKEW Function
  • SLOPE Function
  • SMALL Function
  • STANDARDIZE Function
  • STDEV Function
  • STDEVA Function
  • STDEVP Function
  • STDEVPA Function
  • STEYX Function
  • SUM Function
  • SUMIF Function
  • SUMPRODUCT Function
  • SUMSQ Function
  • SUMX2PY2 Function
  • SUMXMY2 Function
  • TRIMMEAN Function
  • VAR Function
  • VARA Function
  • VARP Function
  • VARPA Function
  • WEIBULL Function
  • ZTEST Function

System functions

The following table provides a list of system functions. Use these functions to work with variables specific to Archer and options to produce dynamic results. For more details on each individual function, see System Functions.

  • COMBINESELECTIONS Function
  • CONTAINS Function
  • CONTENTID Function
  • GETGROUPS Function
  • GETUSERS Function
  • GROUP Function
  • ISCORRECT Function
  • ISEMPTY Function
  • ISNUMBER Function
  • MOSTRECENTVALUE Function
  • NOVALUE Function
  • OTHERTEXT Function
  • REF Function
  • SELECTEDVALUENUMBER Function
  • TRACKINGID Function
  • USER Function
  • USERFIRSTNAME Function
  • USERLASTNAME Function
  • USERMIDDLENAME Function
  • VALUEOF Function
  • WEIGHTEDSCORE Function
  • WEIGHTING Function

Text functions

The following table provides a list of text functions. Use these functions to utilize and manipulate text strings to produce dynamic values. For more details on each individual function, see Text Functions.

  • CONCATENATE Function
  • FIND Function
  • LEFT Function
  • LEN Function
  • LOWER Function
  • MASKEDTEXT Function
  • NUMBERFORMAT Function
  • PROPER Function
  • RIGHT Function
  • STRIPHTML Function
  • SUBSTRING Function
  • TRIM Function
  • UPPER Function

Operators

The following table provides a list of operators offered by the formula builder. Click an operator to see its description.

Category

Operator

Arithmetic

Addition

Division

Exponentiation

Multiplication

Subtraction

Comparison

Equal To

Greater Than

Greater Than or Equal To

Less Than

Less Than or Equal To

Not Equal To

Text Concatenation

Concatenate

Note: If a field included in an addition, subtraction, multiplication, division or comparison operation is empty or null, the value "0" (zero) is used for the field value. The following formula is an exception to this rule:

IF([Sample Field] = 0, "TRUE","FALSE")

In this formula, the Sample Field is not populated with the value "0" if the field is empty or null. Instead, this formula returns TRUE when the Sample Field is empty or null.