Calculation of Date, Time, and Date and Time

You can place the date, time, and date and time fields on a form and those fields can be referred from a formula.

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 a display format in the calculated field, or if you use a formula in the Text field, the calculated results of date and date & time are expressed as a series of numbers, not as a date format (for example, November 13, 2020 will be displayed as 1605225600).
This is because the date values are treated as UNIX time.
To show the calculated results of date and date & time in date format, do the following:

  • Calculated fields: Set the display format of the calculated 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", "Asia/Tokyo")
MM (Month) 01 DATE_FORMAT(date, "MM", "Asia/Tokyo")
M (Month) 1 DATE_FORMAT(date, "M", "Asia/Tokyo")
dd (Day) 04 DATE_FORMAT(date, "dd", "Asia/Tokyo")
d (Day) 4 DATE_FORMAT(date, "d", "Asia/Tokyo")
YYYY/MM/dd (Year/Month/Day) 2020/01/04 DATE_FORMAT(date, "YYYY/MM/dd", "Asia/Tokyo")
YYYY-MM-dd (Year-Month-Day) 2020-01-04 DATE_FORMAT(date, "YYYY-MM-dd", "Asia/Tokyo")
MM/dd 01/04 DATE_FORMAT(date, "MM/dd", "Asia/Tokyo")
M/d 1/4 DATE_FORMAT(date, "M/d", "Asia/Tokyo")

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
    If the date field is empty, you can set the Text field to be empty as well.
    Formula: IF(date="","",DATE_FORMAT(date, "YYYY-M-d", "system"))

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(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 as 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 calculated result to "Hours & minutes".