Formula format definition
Overview
This page introduces points to note when using the formula function.
For formulas, please refer to the following link: "Using formulas"
Formula editor input rules
When directly entering values into the formula editor, please follow the rules below.
| Input value | Rules | Example |
|---|---|---|
| Entire field | Case sensitive | - |
| Field (※1) | Use the system name of each field | PlannedTime |
| Property value | $+[Property system name] | $Priority |
| Date (※2) | Write in YYYY/MM/DD format and enclose in "`" (backquotes) | `2020/04/01` |
| String (※3) | Enclose the value in """ (double quotes) | "High" |
| Logical value | Only true/false is accepted | - |
| Numeric value | Specify directly | 1, 12.3 |
※1 This applies to fields displayed in the "Field list" of the formula editor.
※2 The following date calculations are possible.
- Date + Number = Date (Number + Date is an error)
- Date - Number = Date (Number - Date is an error)
- Date - Date = Number (The difference in days is returned as a number)
- Comparison operation between date types
※3 The following string operations are possible.
- String match comparison
- String + String: String concatenation
List of available functions
The functions that can be used in calculation formulas are listed below.
| Function name | Parameters | Return type | Explanation | Example of use |
|---|---|---|---|---|
| LEN( text ) | text | Number | Returns the length of the string text, which is a string or number. | LEN( "text" ) = 4 |
| FIND( text1 , text2 [ , number ] ) | text1 : String text2 : String number : Number ※1" | Number | If text1 contains text2, returns the beginning position of text2. If it does not contain text2, returns -1. | FIND( "Hello■World!■Hello!", "Hello", 1 ) = 13 |
| MID( text , number1 , number2 ) | text : String number1 : Number number2 : Number | String | Returns the string from number1 with length number2 for text. | MID( "abcdefg" , 2 , 3 ) = "cde" |
| ABS( number ) | number : Number | Number | Returns the absolute value of number. | ABS( -123 ) = 123 |
| CEILING( number ) | number : Number | Number | Returns the smallest integer value equal to or greater than number. | CEILING( 12.3 ) = 13 |
| FLOOR( number ) | number : Number | Number | Returns the largest integer value equal to or less than number. | FLOOR( 12.3 ) = 12 |
| ROUND( number1 [ , number2 ] ) | number1 : Number number2 : Number ※2 | Number | number1 rounded to the number of decimal places specified by number2. Note that midpoints are rounded to the nearest even number. | ROUND( 1.23 , 1 ) = 1.2 |
| MAX( number1 , number2 ) | number1 : Number number2 : Number | Number | Returns the larger value of number1 and number2. (If the values are equal, returns number1.) | MAX( 123 , 456 ) = 456 |
| MIN( number1 , number2 ) | number1 : Number number2 : Number | Number | Returns the smaller value of number1 and number2. (If the values are equal, returns number1.) | MIN( 123 , 456 ) = 123 |
| ADDDAYS( date , number ) | date : Date number : Number" | Date | Returns the date number days after date (※3). | ADDDAYS(`2019/11/22`,1) = `2019/11/23` (2019/11/22 is a Friday) |
| ADDWORKDAYS( date , number ) | date : Date number : Number | Date | Returns the date number days after date, taking into account working days (※3). | ADDWORKDAYS('2019/11/22`,1) = `2019/11/25` (2019/11/22 is a Friday) |
※1 The behavior changes as follows depending on whether or not number is specified.
- If number is specified, the search begins from the position indicated by number.
- If number is omitted, the search begins from the beginning.
※2 If number2 is omitted, it will be rounded to the nearest integer and returned.
※3 If a negative number is specified for number, a past date will be returned.