rex0220 計算式プラグイン 日またがりの時間計算

計算式プラグインで、日またがりの時間計算を行います。
勤務時間、深夜勤務などでよく使われます。

概要

下記の要件で、勤務時間を計算します。

開始時刻： 勤務開始時刻
終了時刻： 勤務終了時刻
日の切替時刻： "06:00"
深夜勤務開始時刻： "22:00"
休憩時間： 時間単位で指定
勤務時間： 開始時刻から終了時刻までの時間から休憩時間を引いた時間

日またがりのある勤務時間を計算

日またがりのある勤務時間を計算する場合、以下のような計算式を使用できます。ここで、日の切り替わる時刻を "06:00" とし、勤務開始時刻と終了時刻をそれぞれ変数として扱います。

日またがりを考慮し、開始時刻と終了時刻が翌日にまたがるかどうかを判定します。
勤務開始時刻が日の切り替わり時刻より前であれば、前日の勤務として扱います（例: 10:00 開始ならば、前日の 10:00 として扱う）。
勤務終了時刻が日の切り替わり時刻より前または同じであれば、翌日の勤務として扱います（例: 03:00 終了ならば、翌日の 03:00 として扱う）。
日またがりを考慮した開始時間と終了時間を用いて、勤務時間を計算します。
休憩時間を時間単位で指定し、勤務時間から休憩時間を差し引きます。

.js

// テーブル.勤務時間 NUMBER
LET(
  切替時刻, "06:00",
  日またがり開始, IF(開始時刻 < 切替時刻, DURATION(-1, "days"), 0),
  日またがり終了, IF(終了時刻 <= 切替時刻, DURATION(1, "days"), 0),
  終了時間, DURATION(終了時刻, "hours"),
  開始時間, DURATION(開始時刻, "hours"),
  休憩, DURATION(休憩時間, "hours"),
  勤務, DURATION_AS(日またがり開始 + 日またがり終了 + 終了時間 - 開始時間 - 休憩, "hours"),
  IF(勤務>0,勤務,0)
)

計算式プラグインの設定

.js

// 年月 DATE (editable)
DATE_FORMAT(年月,"YYYY-MM-01")

// 勤務時間合計 NUMBER
SUMIF(IN(チェック,"OK"),勤務時間)

// 勤務日数 NUMBER
COUNTIF(AND(IN(チェック,"OK"),勤務時間>0),1)

// 深夜時間合計 NUMBER
SUMIF(IN(チェック,"OK"),深夜勤務)

// 深夜回数 NUMBER
COUNTIF(AND(IN(チェック,"OK"),深夜勤務>0),1)

// 休出回数 NUMBER
COUNTIF(AND(IN(チェック,"OK"),区分="休出"),1)

// 有休回数 NUMBER
COUNTIF(AND(IN(チェック,"OK"),区分="有休"),1)

// 代休回数 NUMBER
COUNTIF(AND(IN(チェック,"OK"),区分="代休"),1)

// テーブル SUBTABLE
DATE_ENDOF(年月,"month","DD")

// テーブル.日付 DATE
DATE_ADD(年月,ROWNO(テーブル),"day")

// テーブル.曜日 SINGLE_LINE_TEXT
SUBSTR("日月火水木金土",DATE_FORMAT(日付,"e"),1)

// テーブル.区分 DROP_DOWN (editable)
IFS(
  区分,区分,
  IN(曜日,"土","日"),"休日",
  "平日"
)

// テーブル.勤務時間 NUMBER
LET(
  切替時刻, "06:00",
  日またがり開始, IF(開始時刻 < 切替時刻, DURATION(-1, "days"), 0),
  日またがり終了, IF(終了時刻 <= 切替時刻, DURATION(1, "days"), 0),
  終了時間, DURATION(終了時刻, "hours"),
  開始時間, DURATION(開始時刻, "hours"),
  休憩, DURATION(休憩時間, "hours"),
  勤務, DURATION_AS(日またがり開始 + 日またがり終了 + 終了時間 - 開始時間 - 休憩, "hours"),
  IF(勤務>0,勤務,0)
)

// テーブル.深夜勤務 NUMBER
LET(
  切替時刻, "06:00",
  深夜切替, "22:00",
  深夜開始, IF(AND(開始時刻>=切替時刻, 開始時刻<深夜切替), 深夜切替,開始時刻),
  日またがり開始, IF(開始時刻 < 切替時刻, DURATION(-1, "days"), 0),
  日またがり終了, IF(終了時刻 <= 切替時刻, DURATION(1, "days"), 0),
  終了時間, DURATION(終了時刻, "hours"),
  開始時間, DURATION(深夜開始, "hours"),
  深夜, DURATION_AS(日またがり開始 + 日またがり終了 + 終了時間 - 開始時間, "hours"),
  IF(深夜>0,深夜,0)
)