Overview
 Fields you can Calculate Automatically
 Field Types that can be Used in Formulas
 Configuring Automatic Calculation
 Operators & Functions Available
 Formulas
 Date & Time Formats
 Automatic Calculation Error List
This article details how to configure Automatic Calculation in your apps. You can use the Calculated and Text field types for automatic calculation. When a formula is specified in one of these fields, the value for the field is automatically calculated in the record.
toFields you can Calculate Automatically
Calculated field type:
 Calculations in the Calculated field are treated as numbers.
 Arithmetic operations: addition, subtraction, multiplication, division, exponentiation.
 Add to, or subtract from a date and time field.
 Calculate the number of days, hours, and minutes between two dates and times.
Text field type:
 Calculation results in the Text field are treated as texts.
 Arithmetic operations: addition, subtraction, multiplication, division, exponentiation.
 Concatenate/combine text from multiple fields in to one field.
Field Types that can be Used in Formulas
 Text
 Number
 Calculated
 Date and time
 Date
 Time
 Created datetime
 Updated datetime
 Created by
 Updated by
 Lookup
 Please note, formulas can't refer to a Link field or the Lookup field if the Key Field is a Link.
Configuring Automatic Calculation
How to automatically calculate a Calculated field:
 In the app where you want to create a calculation, click the Gear wheel to navigate to the App Settings.
 Note the field codes of the fields that you wish to use in a formula of the calculation. To do this:
 Place the cursor over the on the field.
 Click Settings.
 Note the value in Field Code at the bottom of the Settings.
 Drag a Calculated field type from the left field selection area to the form.
 Place the cursor over the on this new field, and click Settings.
 Enter a Name.
 Enter a Formula. For details on formulas, see Formulas below.
 Select the display format of the calculation result.
 Define desired decimal places to be displayed.
 Choose an optional currency symbol (i.e. $)
 Click Save.
 Click Save Form.
 Click Update App to apply the changes.
How to automatically calculate a Text field:
 In the app where you want to create a calculation, click the Gear wheel to navigate to the App Settings.
 Note the field codes of the fields that you wish to use in a formula of the calculation. To do this:
 Place the cursor over the on the field.
 Click Settings.
 Note the value in Field Code at the bottom of the Settings.
 Drag a Text field type from the left field selection area to the form.
 Place the cursor over the on this field, and click Settings.
 Enter a Name.
 Select the Calculate automatically check box.
 Enter a Formula. For details on formulas, see Formulas below.
 Click Save.
 Click Save Form.
 Click Update App to apply the changes.
For a list of field types that can be referred by in formulas, see Field Types that can be Used in Formulas.
For operators and functions available for formulas, see Operators & Functions Available.
toOperators & Functions Available
This section explains operators and functions available for formulas for Automatic Calculation.
Operator/Function  Description  Example Formula  

Expression  Calculated Result  
+  Adds numbers.  3+2  5 
  Subtracts numbers. The operator can also be used as unary operator for converting field values to plus or minus. 
32  1 
*  Multiplies numbers.  3*2  6 
/  Divides numbers.  3/2  1.5 
^  Raises a number to a power. From the minus power of 100 to the power of 100 can be calculated. When a decimal is specified for the exponential power, the decimal number is rounded down to the integer. Example: 3^2.5 is converted to 3^2 and the result is 9. 
3^2  9 
&  Concatenates strings.  John & Doe  JohnDoe 
SUM 
Adds numbers. The following format is to be used: 
SUM(1, 2, 3)  6 
DATE_FORMAT 
Change the date and time format and the time zone.

DATE_FORMAT(Date and time, "YYYYMd", "system")  201641 
Please note:
 Operators are applied in the following priority order:
 ^ (Exponentiation)
 */ (Multiplication and division)
 + (Addition and subtraction)
 & (String operator)
Calculating Numbers
 Available arithmetic functions:
 Addition (+)
 Subtraction ()
 Multiplication (*)
 Division (/)
 Exponentiation (^)
 To add numbers in two fields whose field codes are "NumA" and "NumB", formula = NumA + NumB
 To subtract 1000 from the number in the field whose field code is "NumA", and multiply the result by 2, formula = (NumA  1000) * 2
 To cube the number in the field whose field code is "NumA", formula = NumA^3
Representing Positive or Negative Numbers
Use unary arithmetic operators "+" or "" to represent positive values or negative values. Available fields: Calculated, Text.
 To represent the negative number in the field whose field code is "NumA", formula = NumA. When the number in the field whose field code is "NumA" is "100", the result is "100".
 The following calculation can be performed:
 Calculate the number of days, hours, and minutes between two dates and times.
 Add to or subtract from a date and time.
 To calculate the difference between two date and time fields whose field codes are "DatetimeA" and "DatetimeB" respectively, formula = DatetimeA  DatetimeB
 To add one hour to the value of a Time or Number field whose field code is "TimeA", formula = TimeA + (1 * 60 * 60)
Use the string operator "&" to combine text strings. The values in number, date, and time fields can be combined as texts. Available field: Text.
 To combine text from two fields whose field codes are "TextA" and "TextB" into one field, formula = TextA & TextB. When the value in "TextA" is "John" and "TextB" is "Smith", the result is displayed as "JohnSmith".
 To enter a text string in between fields whose field codes are "TextA" and "TextB", enclose it in double quotation marks ("), formula = TextA & " " & TextB. When the value in "TextA" is "John" and "TextB" is "Smith", the result is displayed as "John Smith".
 To sum up values in a column of a table (in the example below, the "Approximate Hours" subtotal), whose field code is "NumA", the formula = SUM(NumA).
 Note: The subtotal column Field type (ie: "Approximate Hours") must be either Calculated or Number.
 The DATE_FORMAT function is used in the following format:
 DATE_FORMAT(Formula, "Date and time format", "Time zone")
 DATE_FORMAT(UNIX time, "Date and time format", "Time zone")
Parameter  Description 
Formula 
For formula, the following fields can be used:
Specify a field with a field code. To add or subtract time to/from date and time, specify time in seconds in the formula. For example, specify "3600" or "60*60*1" for one hour. For one day, specify "24*60*60*1". If the result of the formula is in number, the number is treated as UNIX time. UNIX time is the number of seconds elapsed since 197011 AM 00:00 UTC. It is also called UNIX time stamp. If the date and time is 201641 AM 00:00 (UTC), the UNIX time is "1459468800". 
UNIX time  Specify UNIX time that is less than 10,000,000,000. 
Date and time format  Specify the date and time format of the converted value. For the format, see Date and Time Formats. 
Time Zone  Specify the changed time zone ID or "system". When "system" is specified, the standard time zone set by the administrator is used. The time zone of the field for date and time is fixed at Coordinated Universal Time. To use these fields in formulas, specify the time zone ID "Etc/GMT" of Coordinated Universal Time. When you specify other time zones, you will not obtain the result as you expect. For more information, see Available Time Zones. 
Date & Time Formats
Specify the date and time format by using the format specifiers listed in the following chart. Each format specifier is converted to the corresponding string.
Format  Description  Example (20160401 12:09) 

YYYY  fourdigit year  2016 
MMMM  full name of the month  April 
MMM  threeletter abbreviation of the month  Apr 
MM  twodigit month  04 
M  month without zero padding  4 
dd  twodigit day  01 
d  day without zero padding  1 
a  AM or PM  PM 
KK  hour in the 12 hour clock (0  11)  00 
K  hour in the 12 hour clock (0  11) without zero padding  0 
hh  hour in the 12 hour clock (1  12)  12 
h  hour in the 12 hour clock (1  12) without zero padding  12 
HH  hour in the 24 hour clock (0  23)  12 
H  hour in the 24 hour clock (0  23) without zero padding  12 
kk  hour in the 24 hour clock (1  24)  12 
k  hour in the 24 hour clock (1  24) without zero padding  12 
mm  minute  09 
m  minute without zero padding  9 
ss  second  09 
s  second without zero padding  9 
Z  time zone offset without a colon  +900 
ZZ  time zone offset with two digits  +09:00 
ZZZ  time zone ID  Asia/Tokyo 
 The maximum of 64 characters can be used to specify the date and time format.
 For any format specifier you do not want to be converted, enclose it with single quotation marks (').
 Example of date and time format: D'a'te: d
 Example of the result: Date: 1
 To display a single quotation mark as a string, use two singlequotations in a row.
Example of date and time format: ''
Example of the result: '
 Change the format of Field A to YYYYMd.
 DATE_FORMAT(Field A, "YYYYMd", "system")
 If the value in Field A is "20160401", the calculated result is: 201641.
 Change the time zone of Field A to Pacific Standard Time.
 DATE_FORMAT(Field A, "YYYYMMdd HH:mm", "America/Los Angeles")
 If the value in Field A is "20160401 13:00", the calculated result is 20160331 21:00.
 Concatenate the string in Field A and the created date and time of the record.
 Field A &"" & DATE_FORMAT(Created datetime, "YYYYMMdd", "system")
 If the value in Field A is "Jane Doe" and the created date and time of the record is "20160410 13:00", the calculated result is: Jane Die20160401.
Automatic Calculation Error List
This section explains the errors displayed in fields where a formula is set.
Error  Description 
#CONVERT!  The value cannot be converted. This error is displayed when the calculated result in the calculated field is a string. The result value in the calculated field should be either number, date and time, date, time or hour. 
#N/A!  The field that is referred by the formula has no entry, or the entered value does not match the type of the calculated field. This error is displayed when a string is entered in the number field. 
#PRECISION!  The result value exceeds the number of significant digits. For details on how to set the significant digits, see the following page: Misc App Settings 
#VALUE!  The calculation cannot be performed or the calculated result is invalid. This error is displayed in the following cases:

#FORMULA!  The DATE_FORMAT function is incorrectly specified in the formula. 
#ERROR!  An unexpected error occurred. 
Related Topics:
Comments
Where can I find a list of available time zone values? "Pacific" is not recognized (as expected), but that doesn't leave me clearer on what values will be recognized.
I'm trying to use this formula in Kintone: DATE_FORMAT(FieldA, "YYYY/MM/dd hh:mm a", "system") and it keeps giving me an error message saying that Date_format function does not exist.
What functions or macros are available for calculation? For example how to express:
(someDate  TODAY())/86400 using only the editor?
Hi Heather,
I have created a Support Ticket on your behalf to discuss the error message.
Best,
Larissa
Hi Tony,
I have created a Support Ticket on your behalf. Please note, calculations that require today's date, such as Age, do require customization.
Best,
Larissa
For a list of available time zones, see: https://get.kintone.help/hc/enus/articles/360003208872
Please sign in to leave a comment.