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.

This section provides a setting example using an app where users can enter work hours for a month in a single record.

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

• (1) "Time" field
  This field is used for entering the work start time.

  • Field name: Start time
  • Field code: start_time
    
    

• (2) "Time" field
  This field is used for entering the work end time.

  • Field name: End time
  • Field code: end_time
    
    

• (3) "Calculated" field
  This field calculates how many late-night overtime hours are included in the work hours calculated from the values entered in fields (1) and (2).

  • Field name: Late-night overtime hours
  • Field code: late_night_overtime_hours
  • Formula: Set the following formula.
    Reference: Explanation of the 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
                        )
                      )
                    )
                  )
                )
              )
            )
          )
        )
      )
    )
  )

  
  
  • Display format: Select Hours & minutes (29 hours 47 minutes).

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.

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
• Calculation to be carried out: 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)
• Calculation to be carried out: 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)
• Calculation to be carried out: 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)
• Calculation to be carried out: (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)
• Calculation to be carried out: (5*60*60-start_time)+(2*60*60)+end_time

(4) 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)
• Calculation to be carried out: end_time-22*60*60

(4) 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)
• Calculation to be carried out: 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)
• Calculation to be carried out: 7*60*60

(8) 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)
• Calculation to be carried out: 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)
• Calculation to be carried out: (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)
• Calculation to be carried out: (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)
• Calculation to be carried out: (24*60*60-start_time)+5*60*60

From the link below, you can download an app template with preconfigured formulas.

App template: latenight_overtime.zip (Japanese only)
latenight_overtime.zip

Click the link to download the template file (zip) and import it into kintone without unzipping it.