Skip to main content

Control - Formula

The formula control allows you to define calculation formulas. When any field involved in the calculation is updated, the formula result is automatically recalculated, reducing manual work and input errors.

To ensure worksheet performance, when the number of records exceeds 100,000, updating formula configurations (such as modifying formulas or referenced fields) will not refresh historical data. To update historical data, use Batch Refresh.

The formula control supports three types of calculations:

  • Numeric calculations
  • Date and time calculations
  • Function-based calculations

1. Numeric Calculations​

Example:
In an order detail, fields include product, model, unit price, quantity, and subtotal. The subtotal can be calculated automatically using a formula instead of manual input.

Step 1: Add a Formula Control​

Step 2: Select Calculation Type​

For numeric calculations, select the Number type.

Step 3: Configure the Formula Expression​

Click the expression input box to select fields involved in the calculation, then choose or manually enter operators to build the formula.

Expressions support nested calculations for complex scenarios such as ratio calculations.

The system provides built-in functions. You can search and use them by entering keywords.

Treat Empty Values as Zero​

If Treat empty values as 0 is not enabled, the formula result will be empty when any referenced field is empty.
If enabled, empty values are treated as 0 in the calculation.

Supported Field Types​

  • Number, Currency, Rollup field
  • Formula (not supported when the calculation type is “duration from now”)
  • Foreign field (data storage type)

Step 4: Display Format Settings​

You can configure how the calculation result is displayed, including decimal places, thousand separators, percentage format, and units.

Decimal Places​

Supports up to 14 decimal places. You can define the rounding method:

  • Round up
  • Round down
  • Round half up

Rounding Methods​

Comparison of rounding methods:

ResultDecimal PlacesRound UpRound DownRound Half Up
1.37111.41.31.4
1.37121.381.371.37

Trim Trailing Zeros​

When Trim trailing zeros is enabled, trailing zeros in decimals are hidden.

For example, if the result is 2.8 and the decimal setting is 4 places:

  • Disabled: displays as 2.8000
  • Enabled: displays as 2.8

When enabled, exported values are treated as numeric. Otherwise, they are exported as text.

Percentage Display​

The result is automatically multiplied by 100. For example:

  • Result = 1 → displayed as 100%
  • Result = 0.99 → displayed as 99%

When percentage display is enabled, units (prefix/suffix) are not shown.

2. Date Calculations​

Calculate the Duration Between Two Dates​

Example:
Automatically calculate the project duration based on the start date and end date in a project record.

Calculation Logic

Formatting​

There are two formatting options:

  • Format 1: Start date at 00:00, end date at 24:00
  • Format 2: Start date at 00:00, end date at 00:00

Formatting only applies to date fields without a time component. If a date includes a time value, the actual time is used in the calculation.

Example 1​

Start date: 2020-12-13
End date: 2020-12-14
Output unit: Days

  • Format 1:
    Calculated as 2020-12-13 00:00 → 2020-12-14 24:00 → Result: 2 days

  • Format 2:
    Calculated as 2020-12-13 00:00 → 2020-12-14 00:00 → Result: 1 day

Example 2​

Start date: 2020-12-13
End date: 2020-12-14 12:00
Output unit: Hours

  • Format 1:
    Calculated as 2020-12-13 00:00 → 2020-12-14 12:00 → Result: 36 hours

  • Format 2:
    Same result: 36 hours
    (Because the end date includes a time component, formatting is not applied to it.)

Units, Auto Conversion, and Decimal Places​

  • Unit
    Defines the output unit of the result. Supported units include: year, month, day, hour, minute, and second.

    Example:
    From Sep 1 to Oct 19 is 49 days.

    • Unit = day → displays 49 days
    • Unit = hour → displays 1176 hours
    • Unit = month/year → depends on decimal settings

  • Display Unit
    Allows you to customize the unit label.

  • Auto Conversion
    Example:
    From Sep 1 to Oct 19 is 49 days.
    If Unit = day and Auto Conversion is enabled → displays 1 month 19 days

    Not supported when the unit is set to year.

  • Decimal Places
    Example:
    49 days ≈ 1.63 months
    With 1 decimal place:

    • Round up → 1.7
    • Round down → 1.6
    • Round half up → 1.6

Calculate Working Days Only​

For scenarios such as leave duration calculation, weekends can be excluded by configuring working days.

Add or Subtract a Duration from a Date​

You can calculate a new date by adding or subtracting a duration from a given date.

Example:
If an employee joins on 2021-06-03 with a 2-month probation period, the confirmation date is automatically calculated as 2021-08-03.

Configuration Example:

  1. Select a date field or a fixed date as the base (e.g., hire date).

  2. Define the formula:

    • Must start with + or -
    • Examples:
      • +1M (add 1 month)
      • -1d (subtract 1 day)
      • Combined: +1Y-1M+2d-3m

    Notation:

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

  3. The duration can come from a field value or a fixed number.

  4. Choose the output format (date or date-time).

Calculate the Duration from a Date to Now​

Example:
For a task with a due date, you may want to display “X days remaining” or “X days overdue”.

Configuration Example:

  1. Select the third calculation type: Duration from a date to now
  2. Choose the calculation direction:
    • Target date − today
    • Today − target date
  3. Select the target date field (fixed date or date field value)
Note

The result of “duration from a date to now” is not stored. It is calculated in real time when the page loads.
Therefore:

  • It cannot be used in other formula calculations
  • It cannot be referenced in workflows
  • It cannot be used in charts for aggregation

3. Function-Based Calculations​

The formula control supports complex calculations using functions and returns results based on the selected output format. Supported output types include: text, number, date-time, date, and time.

Limitation:
Formula fields using function calculations cannot be used as input fields for other formula fields.

Example:
Assign grade levels based on a score field. For example, scores below 60 are marked as Fail, while scores greater than or equal to 60 are marked as Pass.

Configuration Steps:

  1. Add a formula control
  2. Select Function as the calculation type
  3. Click to input a function and complete the configuration

Learn more about functions

  1. Select the output format

Was this document helpful?