Skip to main content

Overview of 50+ System Functions

HAP provides over 50 built-in functions across five categories: Math, Date, Text, Logical, and Advanced. These functions cover most common data processing scenarios and can be used in formula fields, default values, and workflow calculations.

Math Functions​

SUM​

Description: Returns the sum of multiple values

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

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

Empty values are treated as 0.

AVERAGE​

Description: Returns the average of multiple values

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

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

Handling empty and zero values:

  • Empty values: Ignored in calculation
    Example: (2 + 4) / 2 = 3

  • Zero values: Included in calculation
    Example: (0 + 2 + 4) / 3 = 2

Try it

MIN​

Description: Returns the smallest value

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

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

Empty values are treated as 0.

MAX​

Description: Returns the largest value

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

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

Empty values are treated as 0.

PRODUCT​

Description: Returns the product of multiple values

Syntax: PRODUCT(value1, value2, value3, ...)`

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

Empty values are treated as 0.

COUNTA​

Description: Counts the number of non-empty fields

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

Example:
Returns 2 if two fields contain values

Try it

ABS​

Description: Returns the absolute value of a number

Syntax: ABS(value)

Example: ABS(-3.991) → 3.991

Try it

INT​

Description: Returns the largest integer less than or equal to the value

Syntax: INT(value)

Example:

  • INT(3.991) → 3
  • INT(-3.991) → -4

Try it

MOD​

Description: Returns the remainder of a division

Syntax: MOD(dividend, divisor)

Notes:

  • Both parameters are required
  • Empty dividend → treated as 0
  • Empty or zero divisor → calculation is skipped

Try it

ROUND / ROUNDUP / ROUNDDOWN​

ROUND​

Description: Rounds a number to a specified number of decimal places

Syntax: ROUND(value, digits)

Example: ROUND(3.14159, 3) → 3.142

ROUNDUP​

Description: Rounds a number away from zero

Syntax: ROUNDUP(value, digits)

Example: ROUNDUP(3.14159, 3) → 3.142

ROUNDDOWN​

Description: Rounds a number toward zero

Syntax: ROUNDDOWN(value, digits)

Example: ROUNDDOWN(3.14159, 3) → 3.141

Notes:

  • Ensure the number of decimal places matches the field configuration
  • If digits is omitted, the result defaults to an integer

Try it

CEILING / FLOOR​

CEILING​

Description: Rounds a number up to the nearest multiple

Syntax: CEILING(value, significance)

Example: CEILING(9, 2) → 10

FLOOR​

Description: Rounds a number down to the nearest multiple

Syntax: FLOOR(value, significance)

Example: FLOOR(9, 2) → 8

POWER​

Description: Returns a number raised to a power

Syntax: POWER(base, exponent)

Example: POWER(4, 3) → 64

LOG​

Description: Returns the logarithm of a number

Syntax: LOG(number, base)

Example: LOG(9, 3) → 2

COUNTBLANK​

Description: Counts the number of empty values

Syntax: COUNTBLANK(value1, value2, value3, ...)`

Example: Returns 2 if two values are empty

COUNTCHAR​

Description: Returns the number of characters in a text field

Syntax: COUNTCHAR(text)

Try it

RANDBETWEEN​

Description: Returns a random integer between two values (inclusive)

Syntax: RANDBETWEEN(min, max)

Try it

NUMBER​

Description: Converts text or other data types into a numeric value

Syntax: NUMBER(text)

PI​

Description: Returns an approximation of the mathematical constant π (the ratio of a circle’s circumference to its diameter). This function does not require any parameters.

Syntax: PI()

Example: Retrieve the value of π

PI()

  • Result: Returns 3.141592653589793

RADIANS​

Description: Converts an angle from degrees to radians. Trigonometric functions (such as SIN, COS, and TAN) require radians as input, so this function is typically used for conversion before calculation.

Syntax: RADIANS(degrees)

Example: Convert degrees to radians

Convert 90 degrees to radians:

RADIANS(90)

  • Result: Returns 1.5707963267949 (90° ≈ 1.5708 radians)

DEGREES​

Description: Converts an angle from radians to degrees. This is the inverse of the RADIANS function and is commonly used to convert calculation results into degrees.

Syntax: DEGREES(radians)

Example: Convert radians to degrees

Convert π radians to degrees:

DEGREES(PI())

  • Result: Returns 180 (π radians = 180°)

SIN​

Description: Returns the sine of an angle specified in radians.
Note: The input must be in radians. If using degrees, convert them with RADIANS first.

Syntax: SIN(radians)

Example: Calculate sine value

SIN(1)

  • Result: Returns 0.841470984807897

Tip: Calculate using degrees

SIN(RADIANS(30)) → 0.5

COS​

Description: Returns the cosine of an angle specified in radians.
Note: The input must be in radians. If using degrees, convert them with RADIANS first.

Syntax: COS(radians)

Example:

COS(1)

  • Result: Returns 0.54030230586814

TAN​

Description: Returns the tangent of an angle specified in radians.
Note: The input must be in radians. If using degrees, convert them with RADIANS first.

Syntax: TAN(radians)

Example:

TAN(1)

  • Result: Returns 1.5574077246549

ASIN​

Description: Returns the arcsine (inverse sine) of a value. The result is returned in radians. Use DEGREES to convert it to degrees if needed.

Syntax: ASIN(number)
(The input must be between -1 and 1)

Example:

ASIN(0.84114709848079)

  • Result: Returns approximately 0.999400825621613

ACOS​

Description: Returns the arccosine (inverse cosine) of a value. The result is returned in radians. Use DEGREES to convert it to degrees if needed.

Syntax: ACOS(number)
(The input must be between -1 and 1)

Example:

ACOS(0.54030230586814)

  • Result: Returns 1

ATAN​

Description: Returns the arctangent (inverse tangent) of a value. The result is returned in radians. Use DEGREES to convert it to degrees if needed.

Syntax: ATAN(number)

Example:

ATAN(1)

  • Result: Returns 0.785398163397448 (≈ 45°)

NORM_S_DIST​

Description: Returns the standard normal distribution (mean = 0, standard deviation = 1).

Syntax: NORM_S_DIST(z, [cumulative])

Parameters:

  • z: The value for which you want the distribution
  • cumulative: Logical value that determines the return type
    • true (default): Returns cumulative distribution
    • false: Returns probability density function

Example:

NORM_S_DIST(1.96, true)

  • Result: Returns 0.975 (indicating a 97.5% cumulative probability)

PERMUT​

Description: Returns the number of permutations for a given number of objects (order matters).

Syntax: PERMUT(total_number, number_chosen)

Parameters:

  • total_number: Total number of items (non-negative integer)
  • number_chosen: Number of items selected (non-negative integer)

Example:

Select and arrange 3 items from 5:

PERMUT(5, 3)

  • Result: Returns 60

COMBIN​

Description: Returns the number of combinations for a given number of objects (order does not matter).

Syntax: COMBIN(total_number, number_chosen)

Parameters:

  • total_number: Total number of items (non-negative integer)
  • number_chosen: Number of items selected (non-negative integer)

Example:

Select 3 items from 5 (order does not matter):

COMBIN(5, 3)

  • Result: Returns 10

Date Functions​

NETWORKDAY​

Description: Calculates the number of working days between two dates. Weekends (Saturday and Sunday) are excluded by default. You can also specify additional holidays to exclude.
The function returns a numeric value and can be used in number, currency, and text fields.

Parameters:

  • Start date and end date are required
  • Holidays (optional): Specify dates to exclude using []. If not provided, only weekends are excluded

Counting logic:
Includes dates greater than or equal to the start date and less than or equal to the end date.
For example, [5th, 7th] includes the 5th, 6th, and 7th → total 3 days.

Syntax:
NETWORKDAY(start_date, end_date, [holiday1, holiday2, ...])

Example 1: Exclude weekends only

Configuration

NETWORKDAY('2024-3-1','2024-3-4')

March 2 and 3 are weekends, so only March 1 and 4 are counted → 2 days

Example 2: Exclude weekends and specific holidays

Configuration

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

Excludes weekends (2nd–3rd) and specified holidays (4th–5th), leaving only March 1 and 6 → 2 days

Try it

MINUTE / HOUR​

Description: Returns the minute or hour from a datetime value

Syntax:

  • HOUR(datetime) → returns 0–23
  • MINUTE(datetime) → returns 0–59

Try it

WEEKDAY​

Description: Returns the day of the week as a number (1–7), where Monday = 1 and Sunday = 7

Syntax:
WEEKDAY(datetime)

Try it

DAY / MONTH / YEAR​

Description: Extracts the day, month, or year from a date

Syntax:

  • DAY(date) → returns 1–31
  • MONTH(date) → returns 1–12
  • YEAR(date) → returns the year (e.g., 2022)

Configuration:

Result:

Try it

DATENOW​

Description: Returns the current date and time. Can be used in datetime and text fields.

Syntax: DATENOW()

Configuration

Result

DATEADD​

Description: Adds or subtracts a time interval from a date and returns a new date/time

Syntax:
DATEADD(date, 'offset', format)

Parameters:

  • offset: Text value in the format:
    (+/-)(number)(unit) Units:

    • 'Y' = year
    • 'M' = month
    • 'd' = day
    • 'h' = hour
    • 'm' = minute

    Examples:

    • '-1d' → subtract 1 day
    • '+3m' → add 3 minutes
    • '+3M' → add 3 months

  • format:

    • 1 = date only
    • 2 = date + time

Example 1: Calculate probation end date (3 months later)

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

Example 2: Add 1 hour to a timestamp

DATEADD('2021-3-6 9:00','+1h',2) → 2021-3-6 10:00

More examples:

  • Start of current week (Monday):
    DATEADD(DATENOW(), CONCAT('-', WEEKDAY(DATENOW())-1, 'd'), 1)

  • End of current week (Sunday):
    DATEADD(DATENOW(), CONCAT('+', 7-WEEKDAY(DATENOW()), 'd'), 1)

If the offset is dynamic, use CONCAT() to construct it.

Try it

DATEIF​

Description: Calculates the time difference between two dates.
The result includes a unit and is returned as text.

Syntax:
DATEIF(start_date, end_date, format, 'unit')

Parameters:

  • start_date / end_date: Required

  • format (required): Specify 1 or 2.
    If the date field includes a time value, the actual timestamp is used.
    If not, the system applies the selected format:

    • format 1:
      Both start and end dates are calculated from 00:00

    • format 2:
      Start date is calculated from 00:00, and end date from 24:00

  • unit (optional):

    • 'Y' = years
    • 'M' = months
    • 'd' = days (default)
    • 'h' = hours
    • 'm' = minutes

Examples:

  • DATEIF('2021-12-1','2021-12-2',1,'d') → 1 day
  • DATEIF('2021-12-1','2021-12-2',2,'d') → 2 days
  • DATEIF('2021-12-1','2021-12-2 12:00',2,'d') → 1 day (rounded down)

Configuration and result:

  • Format 1

  • Format 2

  • Result

Try it

Using DATEIF Results in Further Calculations

Since DATEIF returns text (for example, "6 days"), you need to convert it to a number before performing further calculations.

Example:

To calculate "6 days" + 5 → 11 days

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

Steps:

  1. Use FIND to remove the unit text
  2. Use NUMBER to convert the result to a numeric value
  3. Perform the calculation

WEEKNUM​

Description: Returns the week number of a given date within a year (i.e., the week in which the date falls).

Syntax: WEEKNUM(date, [return_type])

Parameters:

  • date (required): A valid date value
  • [return_type] (optional): Defines how weeks are calculated. Default is 1
    • 1: Week starts on Sunday (Sunday is treated as the first day of the week)
    • 2: Week starts on Monday (Monday is treated as the first day of the week)

Example:

  • Formula: WEEKNUM('2025-02-19', 2)
  • Result: 8
  • Notes: February 19, 2025 falls in the 8th week of the year when weeks start on Monday.

WORKDAY​

Description: Calculates a date a specified number of working days before or after a start date. Weekends (Saturday and Sunday by default) and optional holidays are excluded automatically.

Syntax: WORKDAY(start_date, days, [holidays])

Parameters:

  • start_date (required): The start date for calculation
  • days (required): Number of working days to offset
    • Positive values: future dates
    • Negative values: past dates
  • [holidays] (optional): A list or array of dates to exclude as holidays

Example:

  • Formula: WORKDAY('2025-02-19', 10, ['2025-02-23', '2025-02-28'])
  • Result: 2025-03-06
  • Explanation: Calculates 10 working days after February 19, 2025, excluding weekends and the specified holidays.

WORKDAY_INTL​

Description: An extended version of WORKDAY. It allows you to define custom weekend rules, making it suitable for different regions, industries, or scheduling systems.

Syntax: WORKDAY_INTL(start_date, days, [weekend], [holidays])

Parameters:

  • start_date (required): A valid date
  • days (required): Number of working days to offset
    • Positive values: forward calculation
    • Negative values: backward calculation
  • [weekend] (optional): Defines which days are treated as weekends
    • Uses codes from 1 to 17
    • Default is 1 (Saturday and Sunday)
  • [holidays] (optional): A list or array of holiday dates to exclude

Common weekend codes:

CodeWeekend DaysDescription
1Saturday, Sunday (default)Standard weekend
2Sunday, MondayTwo-day weekend
3Monday, TuesdayTwo-day weekend
4Tuesday, WednesdayTwo-day weekend
5Wednesday, ThursdayTwo-day weekend
6Thursday, FridayTwo-day weekend
7Friday, SaturdayTwo-day weekend
11Sunday onlySingle-day weekend
12Monday onlySingle-day weekend
13Tuesday onlySingle-day weekend
14Wednesday onlySingle-day weekend
15Thursday onlySingle-day weekend
16Friday onlySingle-day weekend
17Saturday onlySingle-day weekend

Example:

WORKDAY_INTL("2026-06-01", 6, 11, ["2026-06-03","2026-06-04"])

Explanation:

  • Start date: 2026-06-01
  • Working days to calculate: 6
  • Weekend rule: Sunday only (code 11)
  • Additional holidays: 2026-06-03 and 2026-06-04

Result: 2026-06-10

How to dynamically maintain holiday lists​

If holidays are stored in a text field (e.g., Holiday List), you do not need brackets ([]) when entering values. Simply separate dates using commas.

  • Text field example: 2026-06-07,2026-06-08

  • Usage in formulas:
    Use SPLIT() to dynamically convert the text into an array for functions such as NETWORKDAY, WORKDAY, or WORKDAY_INTL.

Example:

WORKDAY(start_date, 5, SPLIT(Holiday List, ","))

Text Functions​

CONCAT​

Description: Combines multiple values into a single text string.

Syntax: CONCAT(value1, value2, ...)

Example:
CONCAT(Sales, '-', Kevin Jones)

You can combine field values with static text. For example, concatenate a department field and a name field using a separator (-).

Result: Sales-Kevin Jones

Try it

REPLACE​

Description: Replaces part of a text string with another value.

Syntax: REPLACE(text, start_position, length, new_text)

Parameters:

  • start_position: Starting position (1-based index).
  • length: Number of characters to replace.

Example:

REPLACE('4155552671', 4, 3, '***')

Masks part of a phone number.

Result: 415***2671

Try it

MID​

Description: Extracts a substring from the middle of a text string.

Syntax: MID(text, start_position, length)

Parameters:

  • text (required)
  • start_position (required)
  • length (required)

Example:

MID('ORD-2025-000123', 5, 4)

Extracts the year from an order number.

Result: 2025

Try it

RIGHT​

Description: Extracts characters from the right side of a text string.

Syntax: RIGHT(text, length)

Example:

RIGHT('ORD-2025-000123', 6)

Result: 000123

Try it

LEFT​

Description: Extracts characters from the left side of a text string.

Syntax: LEFT(text, length)

Example:

LEFT('ORD-2025-000123', 3)

Result: ORD

Try it

TRIM​

Description: Removes leading and trailing spaces from text.

Syntax: TRIM(text)

Try it

CLEAN​

Description: Removes all spaces from a text string.

Syntax: CLEAN(text)

Try it

REPT​

Description: Repeats a text value a specified number of times.

Syntax: REPT(text, number_of_times)

Example:

REPT('*', 5)

Result: *****

Try it

LOWER​

Description: Converts all letters in a text string to lowercase.

Syntax: LOWER(text)

Example:

LOWER('JohnDOE')

Result: johndoe

Try it

UPPER​

Description: Converts all letters in a text string to uppercase.

Syntax: UPPER(text)

Example:

UPPER('JohnDoe')

Result: JOHNDOE

Try it

STRING​

Description: Converts a value to text format.

Syntax: STRING(value)

Example:

  • STRING(1 + 5) → 6
  • STRING(1) + STRING(5) → 15

Try it

FIND​

Description: Extracts a substring using start and end delimiters.

Syntax: FIND(text, start_delimiter, end_delimiter)

The delimiters are not included in the result.

Example:

If a field contains: 200*15

  • Extract the first value:
    FIND('20015', '', '')

  • Extract the second value:
    FIND('20015', '', '')

Try it

FINDA​

Description: Extracts multiple substrings and returns them as an array.

Syntax: FINDA(text, delimiter_start, delimiter_end)

Example:

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

Result: A,B,C

Try it

SPLIT​

Description: Splits text into an array using a delimiter.

Syntax: SPLIT(text, "delimiter")

Example:

SPLIT('California/Los Angeles/Westwood', '/')

Result: California,Los Angeles,Westwood

Try it

JOIN​

Description: Joins array elements into a single string using a delimiter.

Syntax: JOIN(array, "delimiter")

Example:

JOIN(Assignees, '-')

Result: John Doe-Jane Smith-Mike Lee

Try it

SUBSTITUTE​

Description: Replaces occurrences of a substring within a text string.

Syntax: SUBSTITUTE(text, old_text, new_text, [instance_num])

Example 1: Replace a specific occurrence

SUBSTITUTE("Apple-Apple-Apple", "Apple", "Banana", 2)

Result: Apple-Banana-Apple

Example 2: Remove characters

SUBSTITUTE("2026-06-08", "-", "")

Result: 20260608

Notes:

  • Case-sensitive
  • All inputs are treated as text

STRREVERSE​

Description: Reverses the order of characters in a text string.

Syntax: STRREVERSE(text)

Example:

STRREVERSE("Hello World")

Result: dlroW olleH

Logical Functions​

IF​

Description: Evaluates a condition and returns different values depending on whether the result is true or false.

Syntax:
IF(expression, value_if_true, value_if_false)

Example 1: Basic condition evaluation
Assign a status based on a score:

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

  • If true: returns Pass
  • If false: returns Fail

Example 2: Nested conditions
Classify scores into multiple levels:

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

  • If Score ≥ 80: returns Excellent
  • If 60 ≤ Score < 80: returns Pass
  • If Score < 60: returns Fail

OR​

Description: Returns true if at least one condition is met. Returns false only when all conditions are false.
Typically used inside an IF function.

Syntax: OR(condition1, condition2, ...)

Example: Multiple valid answers

Allow scoring if any correct option is selected:

IF(OR(Answer == 'Option A', Answer == 'Option B'), 1, 0)

  • If true: returns 1
  • If false: returns 0

Try it

AND​

Description: Returns true only when all conditions are met. Returns false if any condition is not satisfied.
Typically used inside an IF function.

Syntax: AND(condition1, condition2, ...)

Example: Multi-condition validation

An applicant is approved only when both exam and interview scores exceed 85:

IF(AND(WrittenScore > 85, InterviewScore > 85), 'Approved', 'Rejected')

  • If true: returns Approved
  • If false: returns Rejected

Try it

NOT​

Description: Reverses a logical result.
Returns false if the condition is true, and true if the condition is false.

Syntax: NOT(expression)

Example:

  • NOT(2 > 1) → false
    (because 2 > 1 is true)

  • NOT(2 > 3) → true
    (because 2 > 3 is false)

ISBLANK​

Description: Checks whether a field is empty.
Returns true if the field has no value, otherwise returns false.

Syntax: ISBLANK(field)

Example: Handle empty values

Show a default message when a Notes field is empty:

IF(ISBLANK(Notes), 'No notes available', Notes)

  • If true: returns No notes available
  • If false: returns the actual field value

Try it

INCLUDE​

Description: Checks whether a text contains a specified substring.
Returns true if the substring is found, otherwise returns false.

Syntax: INCLUDE(text, search_text)

Example: Text matching

INCLUDE('United States of America', 'States')

  • If true: substring exists in the text
  • If false: substring does not exist

Try it

FALSE​

Description: Returns the boolean value false.
Commonly used as a fixed logical return value.

Syntax: FALSE()

TRUE​

Description: Returns the boolean value true.
Commonly used as a fixed logical return value.

Syntax: TRUE()

Try it

Advanced Functions​

ENCODEURI​

Description:
Encodes a text string into a valid URI. This is useful when storing or processing URLs that contain spaces or non-ASCII characters (such as Chinese characters).

Syntax: ENCODEURI(text)

Example 1: Encode spaces in URLs

Configuration

*Result

Herr %20 represents a space in URL encoding.

Example 2: Encode non-English characters

If a URL contains non-English characters, they will be encoded into a URI-safe format.

Result

Try it

DECODEURI​

Description:
Decodes a URI-encoded string back into readable text.

Syntax: DECODEURI(text)

Configuration

Result

ENCODEURICOMPONENT​

Description:
Encodes a text string as a URI component.
Compared to ENCODEURI, this function encodes more reserved characters and is typically used for encoding query parameters.

Characters such as ; / ? : @ & = + $ , # will be encoded into hexadecimal escape sequences.

Syntax: ENCODEURICOMPONENT(text)

Configuration

Result

Try it

DECODEURICOMPONENT​

Description:
Decodes a URI component encoded by ENCODEURICOMPONENT.

Syntax: DECODEURICOMPONENT(text)

Configuration

Result

DISTANCE​

Description:
Calculates the distance between two location fields.
The result is returned in kilometers (km). To convert to meters, multiply the result by 1000.

Syntax: DISTANCE(location1, location2)

Example: Calculate distance between two locations

Configuration

Result

Try it

Using a fixed location​

If one of the locations is fixed (for example, an office location), you can directly enter coordinates:

DISTANCE(LocationField, "longitude,latitude")

Example: DISTANCE(Check-in Location, "121.4737,31.2304")

How to get coordinates

Enable Show Latitude and Longitude in the location field settings.
After selecting a location in a record, the coordinates will be displayed.

GETPOSITION​

Description:
Retrieves specific details from a location field, such as title, address, or coordinates.

Syntax: GETPOSITION(location, 'property')

Supported properties:

PropertyCode
Location name/title'title'
Full address'address'
Longitude'x'
Latitude'y'
Longitude and latitude'x,y'

Notes:

  • The property code must be enclosed in single quotes and written in lowercase.
  • Not all locations include a title value.

Example: Get full address

Configuration

Result

Try it

Was this document helpful?