This article details** **how to configure** Automatic Calculation** in your apps. You can use the **Calculated** and **Text **field types for automatic calculation in an app. When a formula is specified in one of these fields, the value for the field is automatically calculated in the record.

**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, Author, Last modified by

Configuring Automatic Calculation

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

To automatically calculate a **Text **field:

- Follow steps 1 through 3 above.
- 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.

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

**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 =
**DatetimeA - DatetimeB** - To add one hour to the value of the Time 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**. - 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".

**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 =
**SUM(NumA)**. - Note: The sub-total column Field type (ie: "Approximate Hours") must be either Calculated or Number.

**Text**.

- DATE_FORMAT(
*field code*,*date and time format*,*time zone*). The default value of "time zone" is the system time zone. - DATE_FORMAT(
*number*(*Unix timestamp*),*date and time format*,*time zone*)

Example:

- To format value in the field whose field code is "FieldA" with the format of YYYY/MM/dd hh:mm a, formula =
**DATE_FORMAT(FieldA, "YYYY/MM/dd hh:mm a", "system")** - When the value in the field whose field code is "FieldA" is " 2016‒04‒01 14:00".Then, the result is 2016/04/01 2:00 PM.

Date Formats:

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 |

## Comments

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

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

Please sign in to leave a comment.