Skip to main content

Mathematical Expressions for Fields

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.

calculation_en_1.png

 

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)

calculation_en_2.png

 

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.

calculation_en_3.png

 

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."

calculation_en_4.png

 

Even if you manually change the value of a calculation result field, clicking the "+-×÷" button will re-enter the calculated result.

calculation_en_5.png

 

 

[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
① To find the date approximately 6 months before the conflict date = the day to take action for intention confirmation and employment stabilization measures
(180 days before the 3-year mark)		 In the Sales hierarchy: When a new Sales record is created, calculate "3 years minus 180 days from the contract start date" to determine the date for taking action before the conflict date. Date
Contract start date + 3 years - 180 days = approximately 6 months before the individual-basis limit date
addyear("2026/01/01",3)-180 = 2028/07/05
 ② 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

addday

Add or subtract days (dd)

addmonth

Add or subtract months (MM)

addyear

Add or subtract years (yyyy)

date

Extract only the date (yyyy/MM/dd)

DAY

Extract the day (dd) as a number

hours

Convert time (HH:mm) into hours (a number)

minutes

Convert time (HH:mm) into minutes (a number)

MONTH

Extract the month (MM) as a number

time

Extract only the time (HH:mm)

TODAY()

Get the current date and time

YEAR

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")
       
  • 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)

 

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

 

-> 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)
       
  • 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)

 

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

 

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)
       
  • 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)

 

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

 

Notes
  • Leap years are taken into account in the calculation.
    • Formula　addyear("2024/02/29",1) 
      Output　2025/02/28

 

-> 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")
       
  • Field types whose value can be entered as-is
    • Date-type / Date-Time-type / Age-type
      • Example: date(Resume.U_SAMPLEALIAS)

 

Output= format of the returned value
  • yyyy/MM/dd
    • Example　:　
      　Formula　date("2012/03/04 05:06")
      　Output　2012/03/04

 

-> 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")
       
  • Field types whose value can be entered as-is
    • Date-type / Date-Time-type / Age-type
      • Example: DAY(Resume.U_SAMPLEALIAS)

 

Output= format of the returned value
  • Number
    • Example　:　
      　Formula　DAY("2012/03/04 05:06")
      　Output　4

 

-> 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")
       
  • Field types whose value can be entered as-is
    • Time-type
      • Example: hours(Resume.U_SAMPLEALIAS)

 

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...

 

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")
       
  • Field types whose value can be entered as-is
    • Time-type
      • Example: minutes(Resume.U_SAMPLEALIAS)

 

Output= format of the returned value
  • Number
    • Example　:　
      　Formula　minutes("05:06")
      　Output　306

 

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")
       
  • Field types whose value can be entered as-is
    • Date-type / Date-Time-type / Age-type
      • Example: MONTH(Resume.U_SAMPLEALIAS)

 

Output= format of the returned value
  • Number
    • Example　:　
      　Formula　MONTH("2012/03/04 05:06")
      　Output　3

 

-> 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")
       
  • Field types whose value can be entered as-is
    • Date-Time-type
      • Example: time(Resume.U_SAMPLEALIAS)

 

Output= format of the returned value
  • HH:mm
    • Example　:　
      　Formula　time("2012/03/04 05:06")
      　Output　05:06

 

-> 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")
       
  • Field types whose value can be entered as-is
    • Date-type / Date-Time-type / Age-type
      • Example: YEAR(Resume.U_SAMPLEALIAS)

 

Output= format of the returned value
  • Number
    • Example　:　
      　Formula　YEAR("2012/03/04 05:06")
      　Output　2012

 

-> 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.
 

 

 

Was this article helpful?
10 out of 14 found this helpful
Return to top

Related articles