Article Number：010213
By setting formulas in a kintone app for inputting work hours, you can calculate late-night overtime hours (from 10:00 p.m. on one day to 5:00 a.m. on the following day) for each workday.
○Preparation
Prepare an app for inputting work hours.
To create an app where users can enter work hours for a month in a single record, place a table on the app form and include in it the fields you want users to fill in on each workday.
In order to calculate late-night overtime hours, include the following three fields in the table.
(1) "Time" field: A field for entering the work start time
(2) "Time" field: A field for entering the work end time
(3) "Calculated" field: A field for calculating late-night overtime hours
○Settings of each field
(1) "Time" field
Field name／Field code: Start time
(2) "Time" field
Field name／Field code: End time
(3) "Calculated" field
Field name／Field code: Late-night overtime hours
Formula:
IF(start_time=end_time,7*60*60,
IF(AND(start_time<=5*60*60, start_time<=end_time, end_time<=5*60*60), end_time-start_time,
IF(AND(start_time<=5*60*60, end_time>=5*60*60, end_time<=22*60*60),5*60*60-start_time,
IF(AND(start_time<=5*60*60, end_time>=22*60*60),(5*60*60-start_time)+(end_time-22*60*60),
IF(AND(start_time<=5*60*60, end_time<=start_time),(5*60*60-start_time)+(2*60*60)+end_time,
IF(AND(start_time<=22*60*60, end_time>=22*60*60),end_time-22*60*60,
IF(AND(start_time<=22*60*60, end_time<=5*60*60),2*60*60+end_time,
IF(AND(start_time<=22*60*60, end_time>=5*60*60, end_time<start_time),7*60*60,
IF(AND(start_time>=22*60*60, end_time>=start_time),end_time-start_time,
IF(AND(start_time>=22*60*60, end_time<=start_time, end_time>=22*60*60),(24*60*60-start_time)+5*60*60+(end_time-22*60*60),
IF(AND(start_time>=22*60*60, end_time<=5*60*60),(24*60*60-start_time)+end_time,
IF(AND(start_time>=22*60*60, end_time>=5*60*60, end_time<=22*60*60),(24*60*60-start_time)+5*60*60,0
)
)
)
)
)
)
)
)
)
)
)
)
Show As: Hours & minutes (29 hours 47 minutes)
○Explanation of the formula
This formula uses the IF function to split the calculation flow into multiple branches based on a combination of the start time and end time.
This makes it possible to calculate late-night overtime hours for each workday no matter the value entered for the start time and end time.
Note
- In kintone, values in Date, Time, and Date and time fields are treated as UNIX time. This means that dates and times are treated in seconds, and therefore, you need to use seconds as the unit of time in your formula. For example, you need to specify "60" for one minute and "3600" or "1*60*60" for one hour.
○Conditional branches specified in this formula
(1) The start time and end time are the same (Working for 24 hours straight)
Conditional expression: start_time=end_time
Formula: 7*60*60
(2) The start time: in between 12:00 a.m. and 5:00 a.m.; and the end time: in between the start time and 5:00 a.m.
Conditional expression: AND(start_time<=5*60*60, start_time<=end_time, end_time<=5*60*60)
Formula: end_time-start_time
(3) The start time: in between 12:00 a.m. and 5:00 a.m.; and the end time: in between 5:00 a.m. and 10:00 p.m.
Conditional expression: AND(start_time<=5*60*60, end_time>=5*60*60, end_time<=22*60*60)
Formula: 5*60*60-start_time
(4) The start time: in between 12:00 a.m. and 5:00 a.m.; and the end time: in between 10:00 p.m. and 12:00 a.m.
Conditional expression: AND(start_time<=5*60*60, end_time>=22*60*60)
Formula: (5*60*60-start_time)+(end_time-22*60*60)
(5) The start time: in between 12:00 a.m. and 5:00 a.m.; and the end time: in between 12:00 a.m. and the start time
Conditional expression: AND(start_time<=5*60*60, end_time<=start_time)
Formula: (5*60*60-start_time)+(2*60*60)+end_time
(6) The start time: in between 5:00 a.m. and 10:00 p.m.; and the end time: in between 10:00 p.m. and 12:00 a.m.
Conditional expression: AND(start_time<=22*60*60, end_time>=22*60*60)
Formula: end_time-22*60*60
(7) The start time: in between 5:00 a.m. and 10:00 p.m.; and the end time: in between 12:00 a.m. and 5:00 a.m.
Conditional expression: AND(start_time<=22*60*60, end_time<=5*60*60)
Formula: 2*60*60+end_time
(8) The start time: in between 5:00 a.m. and 10:00 p.m.; and the end time: in between 5:00 a.m. and the start time
Conditional expression: AND(start_time<=22*60*60, end_time>=5*60*60, end_time<start_time)
Formula: 7*60*60
(9) The start time: in between 10:00 p.m. and 12:00 a.m.; and the end time: in between the start time and 12:00 a.m.
Conditional expression: AND(start_time>=22*60*60, end_time>=start_time)
Formula: end_time-start_time
(10) The start time: in between 10:00 p.m. and 12:00 a.m.; and the end time: in between 10:00 p.m. and the start time
Conditional expression: AND(start_time>=22*60*60, end_time<=start_time, end_time>=22*60*60)
Formula: (24*60*60-start_time)+5*60*60+(end_time-22*60*60)
(11) The start time: in between 10:00 p.m. and 12:00 a.m.; and the end time: in between 12:00 a.m. and 5:00 a.m.
Conditional expression: AND(start_time>=22*60*60, end_time<=5*60*60)
Formula: (24*60*60-start_time)+end_time
(12) The start time: in between 10:00 p.m. and 12:00 a.m.; and the end time: in between 5:00 a.m. and 10:00 p.m.
Conditional expression: AND(start_time>=22*60*60, end_time>=5*60*60, end_time<=22*60*60)
Formula: (24*60*60-start_time)+5*60*60
From the link below, you can download an app template with preconfigured formulas.
Click the link to download the template file (zip) and import it into kintone without unzipping it.
Creating apps from template files
＜References＞