Article Number：00104
kintone cannot automatically calculate elapsed years based on "Today" as the reference date.
However, it is possible to calculate elapsed years (e.g., years of employment, age) from the difference between the "Updated Datetime" field and the "Date" field.
This article explains how to calculate age by using the "Updated Datetime" field or the "Date" field and comparing it to a birthdate.
However, it is possible to calculate elapsed years (e.g., years of employment, age) from the difference between the "Updated Datetime" field and the "Date" field.
This article explains how to calculate age by using the "Updated Datetime" field or the "Date" field and comparing it to a birthdate.
An age is calculated by finding the difference between a date of birth and another value. For the other value, use an "Updated datetime" field if you want to calculate an age based on a record's updated datetime, or use a "Date" or "Date and time" field if you want to calculate an age based on a specific date or datetime.
Here, we will describe two methods for calculating an age based on a record's updated datetime: a simplified method that calculates an approximate age without taking leap years into consideration, and a method that calculates an age while taking leap years into consideration.
＜Method 1: Age calculation that does not take leap years into consideration＞
With this method, leap years are not taken into consideration and a year is considered to be 365 days. An age is calculated as the number of years from a date of birth to an updated datetime. The calculated age may be higher than the actual age.
○Preparation
Place the following fields on the app form in App Settings, and set their field names and field codes.
- "Updated datetime" field
・Field name: Updated Datetime
・Field code: Updated_Datetime
- "Date" field
・Field name: Date of Birth
・Field code: Date_of_Birth
- "Calculated" field
・Field name: Age
・Field code: Age
○Setting up the formula
Set the following formula in the "Calculated" field.
Specify the Number of Decimal Places to Display in the "Calculated" field as 0.
- The "Age" field
(Updated_Datetime-Date_of_Birth)/ (60*60*24*365)
○Formula explanation
The number of years between the updated datetime and the date of birth is calculated, with one year being equal to 365 days.
Note
If the "Date of Birth" field is empty, it will be calculated as January 1, 1970.
To make it so the "Age" field is empty when the "Date of Birth" field is empty, set the following formula in the "Age" field.
- IF(Date_of_Birth="","",(Updated_Datetime-Date_of_Birth)/ (60*60*24*365))
＜Method 2: Age calculation that takes leap years into consideration＞
This method calculates an age as the number of years from a date of birth to an updated datetime, while taking into consideration leap years.
○Preparation
Place the following fields on the app form in App Settings, and set their field names and field codes.
- "Updated datetime" field
・Field name: Updated Datetime
・Field code: Updated_Datetime
- "Calculated" field
・Field name: Updated Datetime A
・Field code: Updated_Datetime_A
- "Date" field
・Field name: Date of Birth
・Field code: Date_of_Birth
- "Calculated" field
・Field name: Date of Birth A
・Field code: Date_of_Birth_A
- "Calculated" field
・Field name: Age
・Field code: Age
○Setting up the formula
Set the following formula in the "Calculated" field.
- The "Updated Datetime A" field
DATE_FORMAT(Updated_Datetime,"YYYYMMdd","system")
- The "Date of Birth A" field
DATE_FORMAT(Date_of_Birth,"YYYYMMdd","system")
- The "Age" field
ROUNDDOWN((Updated_Datetime_A-Date_of_Birth_A)/10000,0)
○Formula explanation
The updated datetime and date of birth are converted to values in a date and time format using the DATE_FORMAT function, and then the difference between the two values is calculated.
Since the age is represented from the ten-thousands place of the calculation result, the calculation result is divided by 10,000, and then the resulting number is rounded down to the nearest whole number using the ROUNDDOWN function.
Note
If the "Date of Birth" field is empty, it will be calculated as January 1, 1970.
To make it so the "Age" field is empty when the "Date of Birth" field is empty, set the following formula in the "Age" field.
- IF(Date_of_Birth="","",ROUNDDOWN((Updated_Datetime_A-Date_of_Birth_A)/10000))
＜Reference＞
Ages can also be calculated using the JavaScript customization feature or extensions.
For details, refer to the following pages.
For information on calculating ages with JavaScript customization, refer to the following page on cybozu developer network, a website for developers.
Handling Elapsed Years in kintone (Automatically Update Years Worked and Ages) (Only available in Japanese)
To search for services that are using extensions, refer to the following page. You can search for a keyword by entering it in the search box.
Extensions (Only available in Japanese)
Note
If you add Calculated field or change formula after creating a record, such setting is not reflected in the existing record. You need to update the record so that formula setting is reflected in that record.
FAQ：How to Reflect Changes to Existing Records When Adding/Editing a Calculated Field Later
Copy the permalink
Permanent link copied