Skip to main content

Introduction to 50 System Functions

Five major types of functions are currently supported, math functions, date functions, text functions, logical functions, and advanced functions, including more than 50 specific functions to meet the daily needs of data processing.

I. Math Function​

SUM​

Function: Return the sum of multiple numbers

Usage: SUM ( value1,value2,value3,... )

Example: SUM(3,6,8), Result: 17

If any field is empty, it is calculated as 0.

This function gives the same result as the following custom formula.

  • SUM formula

  • Custom formula

AVERAGE​

Function: Return the average of multiple numbers

Usage: AVERAGE( value1,value2,value3,... )

Example: AVERAGE(3,6,8) , result: 5.67

  • When the field is empty

    For example, find the average of 3 fields. If one of the fields is empty and the other two are 2 and 4, the average value is (2+4)/2=3.

  • When the field is 0

    For example, find the average of 3 fields. If one of the fields is 0, and the other two are 2 and 4, the average is (0+2+4)/3=2.

MIN​

Function: Return the smallest number among multiple numbers

Usage: MIN( value1,value2,value3,... )

Example: MIN(3,6,8), Result: 3

If any field is empty, it is calculated as 0.

MAX​

Function: Return the largest number among multiple numbers

Usage: MAX( value1, value2, value3, ... )

Example: MAX(3,6,8), Result: 8

If any field is empty, it is calculated as 0.

PRODUCT​

Function: Returns the product of multiple numbers

Usage: PRODUCT( value1,value2,value3,... )

Example: PRODUCT(3,6,8), Result: 144

If a field is empty, it is calculated as 0.

It gives the same result as the following custom formula.

  • PRODUCT formula

  • Custom formula

COUNTA​

Function: Count the number of fields that are not null in multiple fields

Usage: COUNTA( field1,field2,field3,... )

Example: COUNTA(Choice1, Choice2, Choice3) , result: 2, it means two fields are not empty.

  • Configuration

  • Effect

ABS​

Function: Calculate absolute value of a number

Usage: ABS( value )

Example:

  • ABS(-3.991) , result: 3.991

INT​

Function: Return the nearest integer that is always less than or equal to the original number

Usage: INT( value )

Example:

  • INT(3.991) , result: 3;

  • INT(-3.991) , result: -4.

MOD​

Function: Return the remainder of the division

Usage: MOD( Dividend,Divisor)

Parameters: Both parameters are required, and can be either field values or static parameters.

Calculated as 0 if the divisor is empty. If the divisor is empty or 0, it is not calculated.

Example:

  • Configuration

  • Effect

ROUND/ROUNDUP/ROUNDDOWN​

1. ROUND​

Function: Specify the number of reserved digits by decimal point to round the number.

Usage: ROUND( numeric field or constant, decimal places)

  • Configuration: ROUND(3.14159,3)

  • Effect

Result: 3.142. If bit 4 is greater than or equal to 5, then enter 1 bit, if bit 4 is less than 5, then just round off.

2. ROUNDUP​

Function: Rounds the number by the specified number of digits in the increasing direction of the absolute value

Usage: ROUNDUP( numeric field or constant, decimal places)

Example: ROUNDUP(3.14159,3) , the result is: 3.142.

Result: 3.142. Whether or not the number after 3 digits is greater than 5, as long as greater than 0 are directly near 1 digit.

3. ROUNDDOWN​

Function: Rounds off numbers by the specified number of digits in the direction of absolute value reduction

Usage: ROUNDDOWN( numeric fields or constants, decimal places)

Example: ROUNDDOWN(3.14159,3)

The result: 3.141. Whether or not the number after 3 digits is less than 5, it is rounded off directly.

Note:

  1. The digits reserved for the results in ROUND, ROUNDDOWN and ROUNDUP should be the same as those in the numeric field.

  2. If you do not write the parameters of the reserved bits in the function, the integer will be taken directly.

POWER​

Function: Calculate the power of a value

Usage: POWER( base number, exponent)

Example: POWER(4,3)

Result: 64.

LOG​

Function: Calculates the logarithm of a value.

Usage: LOG( antilog, base number)

Example: LOG(12,3)

Result: 2.

COUNTBLANK​

Function: Count the number of null values contained in the parameter

Usage: COUNTBLANK( value1,value2,value3)

Example: COUNTBLANK( 12 , , )

Result: 2.

COUNTCHAR​

Function: Count the number of characters in a text field

Usage: COUNTCHAR( text field )

  • Configuration

  • Effect

RANDBETWEEN​

Function: Returns a random integer between two values, negative numbers are also supported.

Usage: RANDBETWEEN( min field, max field)

  • Configuration

    Randomly get an integer between 1 and 10, or possibly 1 or 10.

  • Effect

NUMBER​

Function: Convert text and other types of values to numeric values

Usage: NUMBER( text)

Example:

  • Converts a text field to a numeric field first, and then adds it to other numbers.

  • Add a text field and a numeric field

    If it is a text and numeric operation, the result is a concatenation of the two contents.

    For example, 1 + 5 gives the text content of 15.

Effect

II. Date Function​

NETWORKDAY​

Function: Calculate the number of working days between two dates. National holidays and Saturdays and Sundays are automatically excluded. If a special date is also a holiday, it can be set to remove. The result returned by this function is of numeric type. Numeric fields, amount fields and text fields can use this function.

Parameter Description:

  • Both start date and end date are required.
  • The specified holidays that need to be excluded are not required. If y is not filled in, only Saturday and Sunday will be excluded. If you want to exclude specified holidays, fill in [].

Date to be counted:

The number of days counted is later than or equal to the start date and earlier than the end date. For example, if it is (5th,7th), the days that are counted are the 5th and 6th, 2 business days.

Usage: NETWORKDAY( start date,end date,[date1,date2,...])

Example 1: Exclude Saturday and Sunday only

  • Configuration

    NETWORKDAY( '2022-1-1','2022-1-6')

    The result is {3,4,5} , 3 workdays.

Example 2: Exclude other specified dates except Saturday and Sunday

  • Configuration

    NETWORKDAY( '2022-1-1','2022-1-6',['2022-1-3','2022-1-4'])

    The result is 1 day. The 1st and 2nd are Saturday and Sunday, excluding the 3rd and 4th, only the 5th is a weekday.

Try it

MINTUE/HOUR​

Function: Get the number of hour and minute of the specified date.

Usage:

It is the same method as getting the month, day and year.

  • HOUR(date/time)

    Get the number between 0 and 23.

  • MINTUE(date/time)

    Get the number between 0 and 59.

Try it

WEEKDAY​

Function: Get the day of the specified date, 1 to 7, where Monday is 1 and Sunday is 7.

Usage: WEEKDAY(date/time)

Try it

DAY/MONTH/YEAR​

Function: Get the year, month and day of the specified date and time

Usage:

The usage of the three functions is the same.

  • DAY (date)

    Get the number between 1 and 31.

  • MONTH (date)

    Get the number between 1 and 12.

  • YEAR (date)

    Get the number of the year , e.g. 2022-12-12, the number obtained is 2022.

Configuration:

Effect:

Try it

DATENOW​

Function: Return the current time. This function is available for date fields and text fields.

Usage: DATENOW(). No parameters.

  • Configuration

  • Effect

DATEADD​

Function: Add a period of time to a date or time to get a new date or time.

Usage: DATEADD( date,'time to be added/subtracted', output format )

Parameter:

  • Time to be added/subtracted: text type, formatted as "add/subtract symbol" + "number" + "time unit"

    • Time units: 'Y' - year; 'M' - month; 'd' - day; 'h' - hour; 'm' - minute.

      Example: '-1d' means subtract 1 day; '+3m' means add 3 minutes; '+3M' means add 3 months.

  • Output Format: numeric type. 1 represents the date format; 2 represents the date + time format.

Example 1: Get the date of becoming a regular employee after 3 months based on the date of joining.

Formula: DATEADD( '2021-3-6','+3M',1)

The result is 2021-6-6.

Example 2: Get the reminder time in 1 hour based on the submission time of the work order.

  • Formula: DATEADD( '2021-3-6 9:00','+8h',2)

  • The result is 2021-3-6 10:00.

More Examples:

  • Get the date of Monday of the week in which the current date falls

    • DATEADD(DATENOW(),CONCAT('-',WEEKDAY(DATENOW())-1,'d'),1)

  • Get the date of Monday of the week in which the current date falls

    • DATEADD(DATENOW(),CONCAT('+',7-WEEKDAY(DATENOW()),'d'),1)

Note

If the time to be added is not a fixed parameter, you need to use CONCAT(), e.g., CONCAT('+', the calculated number,'d').

Try it

DATEIF​

Function: Calculate the number of days between two dates (the output is in text format because the input has units).

Usage: DATEIF( start date,end date,1,'output unit' )

Parameters:

  • The start date and end date are required. It can be either a date field or a static date parameter.

  • Formatting method is required. Enter 1 or 2. If the field itself has a time part, the actual time is calculated; if it does not have a time part, the time is calculated as formatted.

    • Format 1, the time format for the start date is 00:00; the time format for the end date is 00:00.

    • Format 2, the time format of the start date is 00:00; the time format of the end date is 24:00.

  • Output units are non-required, 'Y'-year; 'M'-month; 'd'-day; 'h'-hour; 'm'-minute; if this parameter is not specified, the default is 'd'.

Example 1:

  • DATEIF( '2021-12-1','2021-12-2',1,'d' )

    Format 1, calculate the number of days between 2021-12-1 00:00 and 2021-12-2 00:00. The result is 1.

  • DATEIF( '2021-12-1','2021-12-2',2,'d' )

    Format 2, calculate the number of days between 2021-12-1 00:00 and 2021-12-2 24:00. The result is 1.

  • DATEIF( '2021-12-1','2021-12-2 12:00',2,'d' )

    Format 1, calculates the number of days between 2021-12-1 00:00 and 2021-12-2 12:00. The result is 1, because it is less than 2 days, rounded down.

Configuration and effect

  • Duration 1

  • Duration 2

  • Effect:

Try it

How to use the calculation result for further data calculation

For example, the DATEIF function gets a result of 6 days, if you need to add another 5 to get 11. If you add directly, you get a content like "6 days 5". Therefore, you need to remove the unit [days] first.

Reference formula: NUMBER(FIND(DATEIF('2021-3-8','2021-3-14',2,'d'),"", "days"))+5

First use the FIND function to remove the days, then use NUMBER to convert to a numeric format.

III. Text Function​

CONCAT​

Function: stitch multiple contents together to form a new content

Usage： CONCAT( Content 1，Content 2....)

Example: CONCAT( R&D Department, -, Joey)

The content being combined can be either a field or a static parameter. For example, to combine a department field and a name field, a link character - is needed in between.

The result is 'R&D Department-Joey'

Try it

REPLACE​

Function: Replace something in a string with something else.

Usage: REPLACE( target content , start character , number of characters , content to replace )

Parameters

  • Start character: A number. 1 means start from the first character and the first character is also replaced.

  • Number of characters: A number. The number of characters to be replaced. A letter, Chinese character, number, or space are considered as 1 character.

  • Example

  • REPLACE( '19909090909',4,4 , '****' )

    Replace the 4th-7th digits of the phone number, a total of 4 characters, with **** .

    The result is 199****0909.

Configuration

Try it

MID​

Function: Extracts several characters from the content.

Usage: MID( target content , start position , length )

Parameters

  • Target Content: Required

  • Start Position: Numeric, required, i.e., from which character to start.

  • Length: Numeric, required, i.e., how many characters to be extracted.

Example: MID( 412721200511273011,7,4)

It means extracting 4 characters starting from the 7th for of the ID number, and the result is 2005.

Try it

Function: Extracts several characters from the right end of the content

Usage: RIGHT( target content , length )

Parameters

Target content: Required

Length: number, i.e. how many characters to be extracted, if not filled, only the rightmost character will be taken.

Example:RIGHT( '412721200511273011',4)

It means extracting 4 characters from the right side of the ID number, and the result is 3011.

Try it

LEFT​

Function: Extracts a number of characters starting from the left end of the content.

Usage: LEFT( target content , length )

Parameters

Target content: Required

Length: number, i.e. how many characters to be extracted, if not filled, only the leftmost character will be taken.

Example: LEFT( '412721200511273011',2)

It indicates that 2 characters are extracted from the left side of the ID number, and the result is 41.

Try it

TRIM​

Function: Delete the space at the beginning and end of the text.

Usage: TRIM( text 5 )

Try it

CLEAN​

Function: Delete all spaces in the text according to the input target content.

Usage: CLEAN( Text 5 )

Try it

REPT​

Function: Generate duplicate text in specified multiples according to the target content.

Usage: REPT( target content , 2)

Parameters

Target content: Required Multiple: Numeric, required.

Example

REPT('*',5) means to display * repeatedly 5 times, the result is * .

Try it

LOWER​

Function: Replaces all English letters in the content with lowercase letters.

Usage： LOWER( Target content )

Example: LOWER( aaaBBB)

The result is aaabbb.

Try it

UPPER​

Function: Replace all the English letters in the content with uppercase letters.

Usage： UPPER( Target content )

Example: UPPER( aaaBBB)

The result is AAABBB.

Try it

STRING​

Function: Convert content to text format.

Usage: STRING( content1)

Example:

  • STRING( 1+5)

    The result is 6. First calculate 1 plus 5, and then convert it to text.

  • STRING(1)+STRING(5)

    The result is 15, which converts numbers to text first, and then combine the contents.

Try it

FIND​

Function: Extracts content from text from left to right.

Usage: FIND(original content, "start character", "end character")

  • Start character: If empty, it means return from the first character.
  • End character: If it is empty, it means return until the last character.

The returned result does not include the start and end characters.

Example:

Enter the calculation formula (length * width) in the text field, then get the length and width to write to the corresponding field and use the formula field to get the result.

  • Get the length

    If the start character is empty, start at the first character and end at *.

    FIND('200*15',"","*")

  • Get the width

    Start from * and go to the right until the end.

    FIND('200*15',"*","")

Try it

FINDA​

Function: Get multiple paragraphs from a piece of text and combine them into an array.

Usage: FIND(original content, "spacer 1", "spacer 2")

  • Spacer 1: if empty, no result
  • Spacer 2: if empty, no result

The returned result does not include the interval character.

Example:

Get the content inside () from the text and combine it into text.

FINDA("(A)(B)(C)","(",")")

The result is A,B,C.

The arrays composed in the function does not include [], for example, in the above example, it is shown in the field as A,B,C.

Try it

SPLIT​

Function: Split the text according to the specified spacer, and combine the different contents that are split into an array.

Usage: SPLIT(original content, "spacer")

  • Spacer: If the spacer parameter is empty, each character will be split.

The returned result does not include the spacer.

Example:

Split the selected regions and combine them into an array.

SPLIT(region field,"/")

Region Field: Henan Province / Zhoukou City / Fugou County

Result: Henan Province, Zhoukou City, Fugou County

The arrays composed in the function does not include [], for example, in the above example, it is shown in the field as A,B,C.

Try it

JOIN​

Function: Combine all the elements in an array by the specified spacer.

Usage: JOIN(array, "spacer")

Example:

Combine the people selected in the members (multiple choice) field by -.

JOIN(member,'-')

The result is Ada - Joey - Ross.

Try it

IV. Logical Function​

IF​

Function: Set conditional expression, then return different text according to the result TRUE or FALSE.

Usage: IF( expression, content returned when expression is true, content returned when expression is false)

Example 1: Return different assessment based on scores.

IF( score>=60,'pass',`fail')

  • Return pass if the score is greater than or equal to 60.
  • If the score is less than 60, then return fail.

Example 2: Return different assessment based on scores in more details.

IF( Score>=60,IF( Score>=80,'Excellent','Pass'),'Fail')

There would be three levels:

  • >=80 , Excellent
  • >=60, Pass
  • < 60, Fail

OR​

Function: Determines whether a conditional expression or a set of conditional expressions is true. As long as there is an expression for true, it returns TRUE; if all conditions are false, return FALSE. The function is generally not used alone, often used with the IF function.

Usage: OR( expression1, expression2, expression3...)

Examples: In a fill-in-the-blank question, if any of the two answers filled in are correct, you get 1 point and the other answer gets 0 points.

  • Configuration

Try it

AND​

Function: Determines if a conditional expression or a set of conditional expressions is true. As long as one expression is false, it returns FALSE; if all conditions are true, it returns TRUE. This function is generally not used alone, often used with the IF function.

Usage: AND( expression1, expression2, expression3...)

Example: If a candidate's scores are greater than 85 in all subjects, then directly admit.

  • Configuration

Try it

NOT​

Function: Returns the inverse of the logical value of the parameter. Returns FALSE if the conditional expression is true, or TRUE if the expression is false. What is written to the text field is TRUE or FALSE.

Usage: NOT( expression1)

Example:

  • NOT(2>1) ，return FALSE.

  • NOT(2>3) , return TRUE.

ISBLANK​

Function: Determine if the cell is empty, if so, return TRUE, otherwise return FALSE. What is written to the text field is TRUE or FALSE.

Usage: ISBLANK( field)

Try it

INCLUDE​

Function: Determine if a text contains another text, and return TRUE or FALSE. What is written to the text field is TRUE or FALSE.

Usage: INCLUDE( content1,content2 )

Example:

  • INCLUDE( People's Republic of China, People), return TRUE.

Try it

FALSE​

Function: Return FALSE directly, written to text field.

TRUE​

Function: Return TRUE directly, write to text field.

Try it

V. Advanced Function​

ENCODEURI​

Function: Encode URIs when depositing text, and also encode URLs that contain Chinese characters.

Usage: ENCODEURI( text)

Example 1: When the link contains spaces, you can use this function to remove the spaces.

  • Configuration

  • Effect

The %20 here is the transcoding of spaces.

Example 2: When the link contains Chinese, you can use this function to add the code.

  • Effect

Try it

DECODEURI​

Function: Convert the encoding of URI to text, also decode URLs containing Chinese characters.

Usage: DECODEURI( text2)

  • Configuration

  • Effect

ENCODEURICOMPONENT​

Function: Convert text to URI encoding. This function can encode URLs that contain Chinese characters. The method does not encode letters or numbers, nor does it encode ASCLL punctuation, such as - . ! ~ * ' ( ).

Other characters (such as ; / ? : @ & = + $ , # and other punctuation marks used to split URI components) are replaced by one or more hexadecimal escape sequences.

Usage: ENCODEURICOMPONENT( text3)

  • Configuration

  • Effect

Try it

DECODEURICOMPONENT​

Function: It can convert the encoding of URI to text, and also decode URLs that contain Chinese characters. It can decode the URI encoded by the function of encodeURIComponent().

Usage: DECODEURICOMPONENT( text4)

  • Configuration

  • Effect

DISTANCE​

Function: Calculates the distance between two places in kilometers.

Usage: DISTANCE ( position1, position2)

Example: Calculate the distance from a company to a destination.

  • Configuration

  • Effect

Try it

What about fixed positions?​

If one of the positioning fields is a fixed value, the parameter in the function can be written as a fixed coordinate directly. For example, if you need to calculate the distance between the clocking position and the attendance position when clocking in at work, the attendance position is a fixed value.

DISTANCE(Positioning field, "X,Y"), just replace the value of X and Y coordinates.

As shown in the following figure:

How to view the coordinates of a location

Check [Show Latitude and Longitude] when setting the positioning field, then select the target location in the record to view the coordinates.

GETPOSITION​

Function: Get the title, detailed address, latitude and longitude of the location in the positioning field.

Usage: GETPOSITION ( position1, 'required information code')

Message Type - Code

  • Location title, the code is: 'title'
  • Detailed address, the code is: 'address'
  • Get longitude, the code is: 'x'
  • Get latitude, the code is: 'y'
  • Get x and y, the code is: 'x,y'

The first part of the code should be in single quotes, and the codes are in lower case. Not all addresses have a title.

Example: Get the detailed address of the location.

  • Configuration

  • Effect:

Try it

Have questions about this article？ Send us feedback