Control - Formula
Formula controls allow users to configure calculations that automatically update when referenced fields change, reducing manual computation and input errors.
To maintain worksheet performance, when a worksheet contains over 100,000 records, modifying formula configurations (e.g., editing calculation rules or changing referenced fields) will not automatically refresh historical data. Manual refresh is required if historical data updates are needed.
Formula controls support three types of calculations: number operations, date/time operations, and function-based operations.
-
1. Number calculation
Example: Amount = Quantity * Unit Price
After entering values for Quantity and Unit Price, the Amount is automatically calculated. It supports formatting options like thousand separators and percentage display.
-
2. Date calculation
- Calculate the duration between two dates.
- Calculate the duration between a specified date and the current time.
- Add or subtract a duration from a date/time to compute a new date/time.
-
3. Function-based calculation
Use built-in functions to process various field data types and output results in different formats.
-
Example:
I. Number calculation
Use Case: In order details, users need to display the unit price, quantity, and subtotal amount of purchased products. The subtotal amount does not need to be manually entered but can be automatically calculated using a formula field.
1. Add a Formula control
2. Select calculation method
Choose "Number calculation" here.
3. Configure calculation expression
Click the expression input box to select fields for calculation, then choose or manually enter operators.
Supports nested operations for complex formulas (e.g., calculating ratios).
Built-in functions are available. Enter keywords to search and use them.
Treat empty calculation fields as 0
If unchecked ("Treat empty calculation fields as 0"), the formula result will be empty when any referenced field is empty. If checked, empty fields are treated as 0 in calculations.
Supported field types for calculation
-
Number, Currency, Rollup
-
Formula (except those "Time from the moment")
-
Foreign field (only if set to "Store data"; fields set to "Display only" cannot be used).
4. Display format
Customize how results appear: decimal places, thousand separators, percentages, or units.
Decimal place
Supports up to 14 decimal places. Excess digits can be rounded: round up, round down, round half-up.
Rounding examples
| Result | Decimal Places | Round Up | Round Down | Round Half-Up |
|---|---|---|---|---|
| 1.371 | 1 | 1.4 | 1.3 | 1.4 |
| 1.371 | 2 | 1.38 | 1.37 | 1.37 |
Trim the trailing 0
If checked, trailing zeros are omitted (e.g., 2.800 → 2.8).
Additionally, if this option is checked, the data will be exported as number type; if unchecked, it will be exported as text type.
Display as percentage
Multiplies the result by 100 (e.g., 1 → 100%; 0.99 → 99%).
Units (prefix/suffix) are hidden when percentage display is enabled.
II. Date calculation
Calculate duration between two dates
Use Case: Automatically calculate project duration based on start and end dates.
Calculation Logic
Formats
-
Two available formats:
-
Format 1: Start date 00:00, End date 24:00
-
Format 2: Start date 00:00, End date 00:00
-
Formatting only applies to dates without time parts. Dates with time parts will use actual times in calculations.
Example 1
Project start: 2020-12-13 Project end: 2020-12-14 Output unit: Day
-
Using Format 1:
Calculates duration between 2020-12-13 00:00 and 2020-12-14 24:00 → Result: 2 days
-
Using Format 2:
Calculates duration between 2020-12-13 00:00 and 2020-12-14 00:00 → Result: 1 day
Example 2
Project start: 2020-12-13 Project end: 2020-12-14 12:00 Output unit: Hour
-
Using Format 1/Format 2:
Both calculate duration between 2020-12-13 00:00 and 2020-12-14 12:00 → Result: 36 hours.
Because the end date contains time part, actual time is used (only start date gets formatted).
Unit/Auto-conversion/Decimal place
-
Unit
The default output format for calculation results. Users can select from: Year(s), Month(s), Day(s), Hour(s), Minute(s), or Second(s).
Example: The duration between September 1 and October 19 is 49 days.
If "Days" is selected, the result displays as 49 days. If "Hours" is selected, the result displays as 1,176 hours.
-
Display Unit
If not specified, results follow the unit selected above.
-
Auto-conversion
Example: 49 days between September 1 and October 19:
With "Days" selected and "Auto-conversion" enabled → Displays as 1 month 19 days.
Note: Auto-conversion is unavailable if "Years" is selected.
-
Decimal place
Example: 49 days between September 1 and October 19 = ~1.63 months. Three rounding methods (shown for 1 decimal place):
- Round Up: 1.7
- Round Down: 1.6
- Round Half-Up: 1.6
Consider workday only
For calculating leave duration (excluding weekends), users can enable check this option and select your actual business days.
Add or subtract time from a date
Example: Once the hire date and internship period are determined, the regularization date can be automatically calculated.
Formula Field Configuration:
-
Select a date field or fixed date, then add/subtract a period of time from this date field. Here, select the "Hire Date" field.
-
Write the formula.
The formula must start with + or -.
Example: +1M (add 1 month), -1d (subtract 1 day), or +1Y-1M+2d-3m (add 1 year, subtract 1 month, add 2 days, and subtract 3 minutes).
Y (uppercase) = Year M (uppercase) = Month d (lowercase) = Day h (lowercase) = Hour m (lowercase) = Minute
-
The time duration to add/subtract can be either a field value or a fixed number.
-
Set the output format for the new date, either Date only or Date & Time.
Calculate duration between a date and current time
For example, when setting a deadline for a task, you may need a field showing "Remaining X days" or "Overdue X days" to remind team members.
Formula Field Configuration:
-
Select "Time from the moment".
-
Choose whether to calculate: Target date minus today's date, or Today's date minus the target date.
-
Select the target date field, which can be either a fixed date or a date field.
The result of "Time from the moment" is not stored, it is calculated in real-time only when loading the page. Therefore, this formula field cannot be used in other formulas, cannot be referenced in workflows, or is not available in charts.
III. Function-based calculation
The formula control supports configuring complex functions to derive calculation results based on output formats, including Text, Number, Date & Time, Date, and Time.
Formula fields calculated using functions cannot be referenced by other formula fields.
Formula Field Configuration:
-
Add a formula field.
-
Select "Function".
-
Click to input the function.
-
Select the output format.
