Calculation of Date, Time, and Date and Time

Article Number:040502

You can place the date, time, and date and time fields on a form and those fields can be referenced by 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 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
    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", "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)
    Screenshot: Example formulas for calculating time and an example of the displayed results

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.
Screenshot: The display format of the calculation result