Article Number：00789
In kintone, elapsed time can be calculated by using "Calculation" fields to compute the difference between "Start Time" and "End Time" created with "Time" fields.
When entering times that span across dates, the calculation result may sometimes become negative.
This explanation will show how to use the IF function to avoid situations where the calculation result becomes negative.
＜Preparation＞
Place the following fields in the app settings form and set their respective field names and field codes:
- "Time" field
・Field Name: Start Time
・Field Code: Start_Time - "Time" field
・Field Name: End Time
・Field Code: End_Time - "Calculation" field
・Field Name: Elapsed Time
・Field Code: Elapsed_Time
＜Setting the Calculation Formula＞
Set the following formula for the "Calculation" field:
IF(OR(Start_Time="",End_Time=""),"", IF(Start_Time>=End_Time,End_Time+24*60*60-Start_Time,End_Time-Start_Time))
・Display Format: Select "Time (e.g., 26 hours 3 minutes)"
＜Explanation of the Formula＞
The formula uses the IF function to determine whether the end time is later than the start time. If it is, the formula performs a regular subtraction. If the end time is earlier than the start time due to spanning across dates, the formula adds 24 hours before performing the subtraction. This ensures the calculation does not result in a negative value even when crossing dates.
Note
- In kintone, the values of date, time, and datetime fields are treated as UNIX time. Therefore, dates and times are handled in seconds, and formulas are also specified in seconds. For example, 1 minute is entered as "60," 1 hour as "3600" or "1*60*60."
＜Related Pages＞