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