- About the Calculation Feature
- How to Configure
- [Number-type / Currency-type] Formula Examples
- [Date-type / Date-Time-type / Time-type] Formula Examples
-
When a Formula Produces an Error on the Customization Screen
About the Mathematical Expression Feature
You can set up calculations between fields for Number-type, Currency-type, Date-type, Date-Time-type, and Time-type fields.
(For instructions on creating fields, see Creating/Editing Fields.)
- Only Number-type, Currency-type, Date-type, Date-Time-type, Time-type, and Age-type fields within the same resource can be used in a formula.
- Unused fields cannot be set in a formula. (If a field used in a formula is changed to an unused field, the formula will no longer function.)
- Calculations are not performed automatically. (Data is never rewritten automatically. The value stored in a Number-type or Currency-type field is simply a number — it is not a formula.)
-
On the Add/Edit screen, calculations are only performed when you do one of the following.
- When you click the calculation button.
- When a field used in the calculation is focused (e.g., clicked into) and then loses focus (e.g., you click outside the field).
The calculation result is only reflected on the record whose Add/Edit screen is currently open.
Also, calculations do not run for fields that are hidden due to the layout or input pattern in use.
- In other words, when data is updated through any other feature or screen, the calculation is not run and the result is not reflected. (Examples of features where calculations are not run: Quick-Field-Edit / editing a Reference-type field / Bulk Update / Import / data registration or updates via various apps or the API, etc.)
How to Configure
Open the field settings screen and enter your formula in the "Mathematical Expression" field.
Only single-byte alphanumeric characters and the following symbols can be used in a formula: + - * / . , ( ) _: "
Fields used in a formula are specified using their alias.
You can check a field's alias on the field settings screen in the Customization screen.
For example, to calculate the sales amount of a closed deal by multiplying the candidate's expected annual salary by the commission rate, configure it as follows:
Aliases: Sales.U_SALARY (expected annual salary)
Sales.U_PERCENT (commission rate)
Mathematical Expression : Sales.U_SALARY*(Sales.U_PERCENT/100)
On the Add/Edit screen, once a number is entered for the expected annual salary and commission rate, the calculated result is automatically entered as the value.
If you edit a number in a field used in the formula, a warning message will appear reading "The value of {field name} and later fields will change."
Even if you manually change the value of a calculation result field, clicking the "+-×÷" button will re-enter the calculated result.
[Number-type / Currency-type] Formula Examples
In these examples, Field 1's alias is Sales.U_XX1 and Field 2's alias is Sales.U_XX2.
The formula set for Field 3 can be written as shown below.
You can nest formulas (specify a function as the argument of another function).
| Addition |
Sum of the fields: Sales.U_XX1+Sales.U_XX2 : add(Sales.U_XX1,Sales.U_XX2) Add 10 to Field 1: Sales.U_XX1+10 |
|---|---|
| Subtraction |
Subtract the value of Field 2 from Field 1: Sales.U_XX1-Sales.U_XX2 : sub(Sales.U_XX1,Sales.U_XX2) Subtract 100 from Field 1: Sales.U_XX1-100 |
| Multiplication |
Multiply the value of Field 1 by Field 2: Sales.U_XX1*Sales.U_XX2 : mul(Sales.U_XX1,Sales.U_XX2) Calculating consumption tax: Sales.U_XX1*0.08 Calculating the tax-inclusive amount: Sales.U_XX+(Sales.U_XX1*0.08) |
| Division |
Divide Field 1 by Field 2: Sales.U_XX1/Sales.U_XX2 : div(Sales.U_XX1,Sales.U_XX2) Calculating the tax-exclusive amount: Sales.U_XX/1.08 |
| Sum | Total sum of the fields: sum(Sales.U_XX1,Sales.U_XX2) |
| Round | Round the value of Field 1: round(Sales.U_XX1) *Rounded to the number of decimal places specified in the field settings. |
| Round down | Round down the value of Field 1: floor(Sales.U_XX1) *Rounded down to the number of decimal places specified in the field settings. |
| Round up | Round up the value of Field 1: ceil(Sales.U_XX1) *Rounded up to the number of decimal places specified in the field settings. |
| Remainder | Remainder of dividing Field 1 by Field 2: mod(Sales.U_XX1,Sales.U_XX2) |
| Square root | Square root of Field 1: root(Sales.U_XX1) |
[Date-type / Date-Time-type / Time-type] Formula Examples
· An Age-type field can be used in a Date-type or Date-Time-type formula. It is referenced as a date of birth (Date).
· When you need to specify a fixed date or time within a formula, enclose it in "(double quotation marks).
Date: "2025/12/16"
Date and time: "2025/12/16_09:00"
Time-type: "26:00", "40:00" *Values of 48:00 or later can also be entered
· You can nest formulas (specify a function as the argument of another function).
· Date-Time-type fields will vary depending on the time zone set in the user's profile. Date-type and Time-type fields are not affected by time zone. Because of this, if users are in different time zones, the calculation result may differ depending on who performs the operation, so please be aware.
[Example] On the screen of User A, whose time zone is Japan, a Date-Time-type field displays [2026/07/01 15:00]. On the screen of User B, whose time zone is Brazil, it displays [2026/07/01 03:00]. If a Time-type field contains the formula [time(Date-Time-type field)], the result will be [15:00] when User A performs the operation, and [03:00] when User B performs the operation.
* Anywhere in the tables below that a fixed value such as "2025/12/1 12:34" is shown, a field alias can be used instead.
[Date-type / Date-Time-type / Time-type] Restrictions on Addition and Subtraction
*Addition and subtraction using Date-type, Date-Time-type, or Time-type fields only works in the cases described below. (In any other case, it will result in an error.)
Description |
Example |
| Date-type / Date-Time-type ± number of days |
Example: 20 days later "2025/12/01"+20 = "2025/12/21" (Same result as using the addday function) |
| Time-type ± Time-type |
Example: Actual working hours = (End of work time − Start of work time) − (End of break time − Start of break time) ("17:00"-"8:30")-("13:00"-"12:15") ="8:30"-"0:45" = "7:45" · The addition/subtraction is performed in minutes, then the result is converted back to Time-type. |
| Date-type − Date-type |
Example: Lead time = End date − Start date "2025/12/21"-"2025/12/1" = 20 · The difference in days is returned as a Number-type value. |
Example Use Cases
Use case |
Mathematical Expression |
What the calculation does |
Output |
|---|---|---|---|
| ① Calculating the compliance date (1 day before 3 years later) |
In the Sales hierarchy: | When a new Sales record is created, calculate 3 years minus 1 day from the contract start date to determine the deadline for the compliance date. | Date |
| Contract start date + 3 years − 1 day = individual compliance date (the day before this date is the last day the person can work) | |||
| addyear(2026/01/01, 3)−1＝2028/12/31 | |||
| ② Contract renewal alert date (renewal notice date) |
(45 days is an arbitrary number of days) In the Employment hierarchy: |
Calculate contract end date − 45 days to determine the renewal notice date. | Date |
| Contract end date − 45 days = Contract renewal alert date | |||
| addday(2025/03/31, -45) = 2025/02/14 |
[Date-type / Date-Time-type / Time-type] Functions Available in Formulas
Click a function name to jump to the relevant section. (Alphabetical order)
| Function | Overview |
|---|---|
Add or subtract days (dd) |
|
Add or subtract months (MM) |
|
Add or subtract years (yyyy) |
|
Extract only the date (yyyy/MM/dd) |
|
Extract the day (dd) as a number |
|
Convert time (HH:mm) into hours (a number) |
|
Convert time (HH:mm) into minutes (a number) |
|
Extract the month (MM) as a number |
|
Extract only the time (HH:mm) |
|
Get the current date and time |
|
Extract the year (yyyy) as a number |
addday
Add or subtract days (dd)
Syntax
- addday(input value, number)
- Specify the amount to add or subtract as a positive or negative integer in the "number" part.
Input = formats accepted as the "input value"
- yyyy/MM/dd HH:mm
- Example when specifying a fixed value: addday("2012/03/04 05:06")
- yyyy/MM/dd
- Example when specifying a fixed value: addday("2012/03/04")
- Example when specifying a fixed value: addday("2012/03/04")
-
Field types whose value can be used as-is as the "input value"
- Date-type / Date-Time-type / Age-type
- Example: addday(Resume.U_SAMPLEALIAS, 9)
- Date-type / Date-Time-type / Age-type
Output= format of the returned value
- Returns a value in the same date format as the input value.
- Example 1 :
Formula addday("2012/03/04 05:06", 1)
Output 2012/03/05 05:06 - Example 2 :
Formula addday("2012/03/04", -1)
Output 2012/03/03
- Example 1 :
Notes
- ＊＊＊＊＊＊
- ＊＊＊＊＊＊
-> Back to the top of this section
addmonth
Add or subtract months (MM)
Syntax
- addmonth(input value, number)
- Specify the amount to add or subtract as a positive or negative integer in the "number" part.
Input = formats accepted as the "input value"
- yyyy/MM/dd HH:mm
- Example when specifying a fixed value: addmonth("2012/03/04 05:06", 1)
- yyyy/MM/dd
- Example when specifying a fixed value: addmonth("2012/03/04", -1)
- Example when specifying a fixed value: addmonth("2012/03/04", -1)
-
Field types whose value can be used as-is as the "input value"
- Date-type / Date-Time-type / Age-type
- Example: addmonth(Resume.U_SAMPLEALIAS, 9)
- Date-type / Date-Time-type / Age-type
Output= format of the returned value
- Returns a value in the same date format as the input value.
- Example 1 :
Formula addmonth("2012/03/04 05:06", 1)
Output 2012/04/04 05:06 - Example 2 :
Formula addmonth("2012/03/04", -1)
Output 2012/02/04
- Example 1 :
Notes
- If, after the calculation, the day (dd) value exceeds the last valid day of the resulting month, the last day of that month is returned.
- Example 1 :
Formula addmonth("2012/10/31", 1)
Output 2012/11/30 - Example 2 :
Formula addmonth("2012/03/31", -1)
Output 2012/2/28
-> Back to the top of this section
addyear
Add or subtract years (yyyy)
Syntax
- addyear(input value, number)
- Specify the amount to add or subtract as a positive or negative integer in the "number" part.
Input = formats accepted as the "input value"
- yyyy/MM/dd HH:mm
- Example when specifying a fixed value: addyear("2012/03/04 05:06", 1)
- yyyy/MM/dd
- Example when specifying a fixed value: addyear("2012/03/04", -1)
- Example when specifying a fixed value: addyear("2012/03/04", -1)
-
Field types whose value can be used as-is as the "input value"
- Date-type / Date-Time-type / Age-type
- Example: addyear(Resume.U_SAMPLEALIAS, 9)
- Date-type / Date-Time-type / Age-type
Output= format of the returned value
- Returns a value in the same date format as the input value.
- Example 1 :
Formula addyear("2012/03/04 05:06", 1)
Output 2013/03/04 05:06 - Example 2 :
Formula addyear("2012/03/04", -1)
Output 2011/03/04
- Example 1 :
Notes
- Leap years are taken into account in the calculation.
- Formula addyear("2024/02/29", 1)
Output 2025/02/28
- Formula addyear("2024/02/29", 1)
-> Back to the top of this section
date
Extract only the date (yyyy/MM/dd)
Syntax
- date(input value)
Input = formats accepted as the "input value"
- yyyy/MM/dd HH:mm
- Example when specifying a fixed value: date("2012/03/04 05:06")
- yyyy/MM/dd
- Example when specifying a fixed value: date("2012/03/04")
- Example when specifying a fixed value: date("2012/03/04")
-
Field types whose value can be entered as-is
- Date-type / Date-Time-type / Age-type
- Example: date(Resume.U_SAMPLEALIAS)
- Date-type / Date-Time-type / Age-type
Output= format of the returned value
- yyyy/MM/dd
- Example :
Formula date("2012/03/04 05:06")
Output 2012/03/04
- Example :
Notes
- ＊＊＊＊＊＊
- ＊＊＊＊＊＊
-> Back to the top of this section
DAY
Extract the day (dd) as a number
Syntax
- DAY(input value)
Input = formats accepted as the "input value"
- yyyy/MM/dd HH:mm
- Example when specifying a fixed value: DAY("2012/03/04 05:06")
- yyyy/MM/dd
- Example when specifying a fixed value: DAY("2012/03/04")
- Example when specifying a fixed value: DAY("2012/03/04")
-
Field types whose value can be entered as-is
- Date-type / Date-Time-type / Age-type
- Example: DAY(Resume.U_SAMPLEALIAS)
- Date-type / Date-Time-type / Age-type
Output= format of the returned value
- Number
- Example :
Formula DAY("2012/03/04 05:06")
Output 4
- Example :
Notes
- ＊＊＊＊＊＊
- ＊＊＊＊＊＊
-> Back to the top of this section
hours
Convert time (HH:mm) into hours (a number)
Syntax
- hours(input value)
Input = formats accepted as the "input value"
- HH:mm
- Example when specifying a fixed value: hours("05:06")
- Example when specifying a fixed value: hours("05:06")
-
Field types whose value can be entered as-is
- Time-type
- Example: hours(Resume.U_SAMPLEALIAS)
- Time-type
Output= format of the returned value
- Number
- Example 1 :
Formula hours("08:12")
Output 8.2 - Example 2 :
Formula hours("08:47")
Output 8.783333...
- Example 1 :
Notes
- Returns the result of HH + mm/60. No "rounding" is performed on the decimal portion.
-> Back to the top of this section
minutes
Convert time (HH:mm) into minutes (a number)
Syntax
- minutes(input value)
Input = formats accepted as the "input value"
- HH:mm
- Example when specifying a fixed value: minutes("05:06")
- Example when specifying a fixed value: minutes("05:06")
-
Field types whose value can be entered as-is
- Time-type
- Example: minutes(Resume.U_SAMPLEALIAS)
- Time-type
Output= format of the returned value
- Number
- Example :
Formula minutes("05:06")
Output 306
- Example :
Notes
- Returns the result of HH×60 + mm.
-> Back to the top of this section
MONTH
Extract the month (MM) as a number
Syntax
- MONTH(input value)
Input = formats accepted as the "input value"
- yyyy/MM/dd HH:mm
- Example when specifying a fixed value: MONTH("2012/03/04 05:06")
- yyyy/MM/dd
- Example when specifying a fixed value: MONTH("2012/03/04")
- Example when specifying a fixed value: MONTH("2012/03/04")
-
Field types whose value can be entered as-is
- Date-type / Date-Time-type / Age-type
- Example: MONTH(Resume.U_SAMPLEALIAS)
- Date-type / Date-Time-type / Age-type
Output= format of the returned value
- Number
- Example :
Formula MONTH("2012/03/04 05:06")
Output 3
- Example :
Notes
- ＊＊＊＊＊＊
- ＊＊＊＊＊＊
-> Back to the top of this section
time
Extract only the time (HH:mm)
Syntax
- time(input value)
Input = formats accepted as the "input value"
- yyyy/MM/dd HH:mm
- Example when specifying a fixed value: time("2012/03/04 05:06")
- Example when specifying a fixed value: time("2012/03/04 05:06")
-
Field types whose value can be entered as-is
- Date-Time-type
- Example: time(Resume.U_SAMPLEALIAS)
- Date-Time-type
Output= format of the returned value
- HH:mm
- Example :
Formula time("2012/03/04 05:06")
Output 05:06
- Example :
Notes
- ＊＊＊＊＊＊
- ＊＊＊＊＊＊
-> Back to the top of this section
TODAY()
Get the current date and time
Syntax
- TODAY()
Output= format of the returned value
- yyyy/MM/dd HH:mm
- Example : Output "2012/03/04 05:06"
Notes
- This is the date and time at which the calculation was run.
See About the Calculation Feature above for details on when calculations are triggered. - Returns the date and time interpreted in the time zone of the user performing the operation.
-> Back to the top of this section
YEAR
Extract the year (yyyy) as a number
Syntax
- YEAR(input value)
Input = formats accepted as the "input value"
- yyyy/MM/dd HH:mm
- Example when specifying a fixed value: YEAR("2012/03/04 05:06")
- yyyy/MM/dd
- Example when specifying a fixed value: YEAR("2012/03/04")
- Example when specifying a fixed value: YEAR("2012/03/04")
-
Field types whose value can be entered as-is
- Date-type / Date-Time-type / Age-type
- Example: YEAR(Resume.U_SAMPLEALIAS)
- Date-type / Date-Time-type / Age-type
Output= format of the returned value
- Number
- Example :
Formula YEAR("2012/03/04 05:06")
Output 2012
- Example :
Notes
- ＊＊＊＊＊＊
- ＊＊＊＊＊＊
-> Back to the top of this section
When a Formula Produces an Error on the Customization Screen
If a formula produces an error, please check the following points:
· Are any numbers or symbols entered as full-width characters instead of half-width?
· Are there any unnecessary spaces?
· Is a field used in the formula hidden?
· Does the formula use a field that is not Number-type, Currency-type, Date-type, Date-Time-type, or Time-type?
· Does the formula reference the field's own alias?
●Error when a character other than the allowed characters is used
●Unmatched parentheses error
For example, an opening parenthesis with no closing parenthesis, etc.
Error message: There is bracket error in the expression
●Alias reference error
The Mathematical Expression contains a misspelled alias, a field type that cannot be used in a formula, a hidden field, etc.
Error message: There is invalid alias or function in the expression
●Result type mismatch error
The field type and the type of the calculation result do not match.
Error message: The result of the calculation is not a '〇〇' type. Please review the expression.
However, in the following cases, the type is automatically converted to match the field type.
Field type |
Calculation result |
| Date-type | Date-Time-type |
| Date-Time-type | Date-type *Filled in with 00:00 |
| Time-type | Date-Time-type |
●Circular reference error
The formula of a field referenced within another field's formula (or a field referenced further down that chain) refers back to the alias of the field currently being edited.
Error message: There is circular reference in the expression
●Other errors
Error message: There is an error in the calculation formula.