Calculating Late-Night Overtime Hours

Article Number:040797

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.

Setting example

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

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

Example of the table where the fields are placed

Settings of each field

  • (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).

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.

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

App template

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

App template: latenight_overtime.zip (Japanese only)

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