1. Kintone Help Center
  2. Using Kintone
  3. Building Apps

Automatic Calculation

Avatar
Kevin Veloira
Follow

Overview

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. 

Fields 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: 

  1. In the app where you want to create a calculation, click the Gear wheel Screen_Shot_2017-07-19_at_9.43.03_AM.png to navigate to the App Settings.
  2. 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.
  3. Drag a Calculated field type from the left field selection area to the form.
  4. Place the cursor over the on this new field, and click Settings.
  5. Enter a Name.
  6. Enter a Formula. For details on formulas, see Formulas below.
  7. Select the display format of the calculation result. 
  8. Define desired decimal places to be displayed.
  9. Choose an optional currency symbol (i.e. $)
  10. Click Save.
  11. Click Save Form. 
  12. Click Update App to apply the changes.

How to automatically calculate a Text field: 

  1. In the app where you want to create a calculation, click the Gear wheel Screen_Shot_2017-07-19_at_9.43.03_AM.png to navigate to the App Settings.
  2. 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.
  3. Drag a Text field type from the left field selection area to the form.
  4. Place the cursor over the on this field, and click Settings.
  5. Enter a Name.
  6. Select the Calculate automatically check box.
  7. Enter a Formula. For details on formulas, see Formulas below.
  8. Click Save.
  9. Click Save Form. 
  10. 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. 

Operators & 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.		 3-2 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(Value1, Value2, Value3, ...)
For "Value", specify the code of a "Number" field, the formula that results in a numerical value, or a number.

The operator can function as '+' operator as well as sum values of fields in a table.		 SUM(1, 2, 3) 6
 DATE_FORMAT

Change the date and time format and the time zone.


Changing the Date and Time Format and the Time Zone (DATE_FORMAT Function)


The DATE_FORMAT function can only be used in the "Text" field.

 DATE_FORMAT(Date and time, "YYYY-M-d", "system") 2016-4-1

Please note:

  • Operators are applied in the following priority order:
    • ^ (Exponentiation)
    • */ (Multiplication and division)
    • +- (Addition and subtraction)
    • & (String operator)
Formulas

Calculating Numbers

You can calculate formulas using numbers in both the Calculated and Text fields. Available fields for formulas: Calculated, Number.
  • Available arithmetic functions:
    • Addition (+)
    • Subtraction (-)
    • Multiplication (*)
    • Division (/)
    • Exponentiation (^)
  • To add numbers in two fields whose field codes are "NumA" and "NumB", formula is NumA + NumB
  • To subtract 1000 from the number in the field whose field code is "NumA", and multiply the result by 2, formula is (NumA - 1000) * 2
  • To cube the number in the field whose field code is "NumA", formula is 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 is -NumA. When the number in the field whose field code is "NumA" is "100", the result is "-100".
Calculating Time
 
Times must be specified in seconds. For example, one hour is specified as 3,600 seconds. Available field: Calculated.
 
  • 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 is DatetimeA - DatetimeB
  • To add one hour to the value of a Time or Number field whose field code is "TimeA", formula is TimeA + (1 * 60 * 60)
Concatenating Text from Multiple Fields

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 is 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 is TextA & " " & TextB. When the value in "TextA" is "John" and "TextB" is "Smith", the result is displayed as "John Smith". 
SUM Function

You can use the SUM function to sum up numbers or dates that are located in a table. Available fields: Calculated, Text. 
  • 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 is SUM(NumA).
  • Note: The sub-total column Field type (ie: "Approximate Hours") must be either Calculated or Number.Sep-07-2017_10-33-46.gif
DATE_FORMAT
 
This function enables you to specify the format for the date, time and time zone formats. Available field: Text.
  • 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:

  • Date and time
    • This field can be used only when the date and time is between 1970-1-1 AM 00:00 UTC (AM 9:00 JST) and 2050-1-1 AM 00:00 UTC (AM 9:00 JST).
  • Date
    • This field can be used only when the date is between 1970-1-1 and 2286-11-20.
  • Time
  • Created datetime
  • Updated datetime
  • Number
  • Calculated

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".
Example: Date + (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 1970-1-1 AM 00:00 UTC. It is also called UNIX time stamp. If the date and time is 2016-4-1 AM 00:00 (UTC), the UNIX time is "1459468800".

The number must be greater than -1 and less than 10,000,000,000. The decimal part is rounded down and removed.
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 (2016-04-01 12:09)
YYYY four-digit year 2016
MMMM full name of the month April
MMM three-letter abbreviation of the month Apr
MM two-digit month 04
M month without zero padding 4
dd two-digit 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
 
Please note: 
  • 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 single-quotations in a row.
    Example of date and time format: ''
    Example of the result: '
Example Formulas 
  • Change the format of Field A to YYYY-M-d.
    • DATE_FORMAT(Field A, "YYYY-M-d", "system")
    • If the value in Field A is "2016-04-01", the calculated result is: 2016-4-1.
  • Change the time zone of Field A to Pacific Standard Time.
    • DATE_FORMAT(Field A, "YYYY-MM-dd HH:mm", "America/Los_Angeles")
    • If the value in Field A is "2016-04-01 13:00", the calculated result is 2016-03-31 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 "2016-04-10 13:00", the calculated result is: Jane Doe-20160401.

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:
  • The type of the values for calculation is different.
    Example: Adding a string to a value
  • The calculation cannot be performed.
    Example: Dividing a number by 0
#FORMULA! The DATE_FORMAT function is incorrectly specified in the formula.
#ERROR! An unexpected error occurred.

 

Related Topics:

Available Time Zones

 

Was this article helpful?
6 out of 11 found this helpful

Comments

6 comments
Sort by Date Votes
  • Avatar
    Nim G

    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.

    0
    Comment actions Permalink
  • Avatar
    HeatherJohnson

    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.

    0
    Comment actions Permalink
  • Avatar
    TonyMoores

    What functions or macros are available for calculation? For example how to express:
    (someDate - TODAY())/86400 using only the editor?

    0
    Comment actions Permalink
  • Avatar
    Larissa Squires

    Hi Heather,

    I have created a Support Ticket on your behalf to discuss the error message.

    Best,
    Larissa

    0
    Comment actions Permalink
  • Avatar
    Larissa Squires

    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

    0
    Comment actions Permalink
  • Avatar
    Nim G
    (Edited )

    For a list of available time zones, see: https://get.kintone.help/hc/en-us/articles/360003208872

    0
    Comment actions Permalink

Please sign in to leave a comment.

Related articles