Calculation of Date, Time, and Date and Time
You can place "Date", "Time", and "Date and time" fields on a form and have them be referenced by formulas.
How Values in "Date," "Time," and "Date and Time" Fields Are Treated
The values in the "Date," "Time," and "Date and time" fields are treated as UNIX time. UNIX time is the number of seconds elapsed since 1970-1-1 00:00 AM UTC (Coordinated Universal Time). It is also called UNIX time stamp. For example, 2020-11-30 15:00 UTC is "1606748400" in UNIX time.
In kintone, the date and the date and time are treated in seconds. You need to specify them in seconds in the formulas as well. For example, enter "60" to indicate one minute. Enter "3600" or "60*60" to indicate one hour, and enter "86400" or "60*60*24" to indicate one day.
Example: The future date that is 2 days away from a specific date
Date + (60*60*24*2)
Calculating Date
You can calculate the date by using the formula in the "Calculated" field.
For example, you can perform the following calculations.
- Calculate the future date that is N days away from the "Date" field value
Example: A formula to calculate the end date of the trial period (30 days)
Start date + (60*60*24*30)
Display Format for Calculated Date
If you have specified Number as the display format of the "Calculated" field, or if the formula is set for a "Text" field, the result returned by the formula to calculate date or date & time is displayed as a series of numbers, not in a date format (for example, November 13, 2020 is displayed as 1605225600).
This is because the date values are treated as UNIX time.
To show the calculation results of date and date & time in date format, do the following:
- "Calculated" fields: Set the display format of the calculation results to "Date & time" or "Date"
- "Text" fields: Write a formula using DATE_FORMAT function as described below
| Format | Example | Formula |
|---|---|---|
| YYYY (Year) | 2020 | DATE_FORMAT(Date, "YYYY", "Etc/GMT") |
| MM (Month) | 01 | DATE_FORMAT(Date, "MM", "Etc/GMT") |
| M (Month) | 1 | DATE_FORMAT(Date, "M", "Etc/GMT") |
| dd (Day) | 04 | DATE_FORMAT(Date, "dd", "Etc/GMT") |
| d (Day) | 4 | DATE_FORMAT(Date, "d", "Etc/GMT") |
| YYYY/MM/dd (Year/Month/Day) | 2020/01/04 | DATE_FORMAT(Date, "YYYY/MM/dd", "Etc/GMT") |
| YYYY-MM-dd (Year-Month-Day) | 2020-01-04 | DATE_FORMAT(Date, "YYYY-MM-dd", "Etc/GMT") |
| MM/dd | 01/04 | DATE_FORMAT(Date, "MM/dd", "Etc/GMT") |
| M/d | 1/4 | DATE_FORMAT(Date, "M/d", "Etc/GMT") |
Note: If "Date" Field Is Empty
If the "Date" field that is referred by DATE_FORMAT function is empty, the "Text" field shows 1970-01-01.
As a workaround, you can do the following.
- Set the default value for the "Date" field
- Specify an IF function formula in the "Text" field
You can make it so that if the "Date" field is empty, the "Text" field is empty as well.
Formula: IF(Date="","",DATE_FORMAT(Date, "YYYY-M-d", "Etc/GMT"))
Calculating Time
You can calculate time by using the formula in the "Calculated" field.
For example, you can perform the following calculations.
- Calculating time difference between two times
Example: Formula to calculate working hours in a day
Finish-Start-Break*60 - Addition and subtraction of date/time, and time
Example: Formula to calculate the total working hours in a month
SUM(Working_Hours)
Display Format of the Calculated Time
If you have specified Number as a display format of the "Calculated" field, the calculated time is expressed as a series of numbers, not in a time format.
This is because the time values are treated as UNIX time.
To show the calculated time in time format, set the display format of the calculation result to Hours & minutes.
