Numbersで出退勤時間から勤務時間を計算する方法

iPadでも使える表計算ソフト Numbersで、勤怠管理の表を作りました。

Numbersで給与計算表を作る場合、セルの書式を時間にする場合は日付もセットになるため、日をまたぐ場合は日付も変更しないといけません。

また、時間同士を引き算すると期間という書式に切り替わります。あまり詳しく検証していないのですが、思い通りの仕様にできなさそう。

そこで時間と分でセルを分けて入力きて勤務時間を計算し、深夜手当が発生する場合はその時間を算出させました。

Numbersデータ

https://www.iehohs.com/ftp/numbers-attendance-list.numbers

• 始業時刻と終業時刻の各セルに時・分を入れるだけで拘束時間を計算。
• 拘束時間の内、22:00〜5:00は深夜拘束時間として計算。
• 休憩時間を引いた時間を実働時間として計算
• 時給に実働時間、深夜実働×25%をして計算、分は15分刻みで25%ずつ加算

ここから説明に入りますが、わかりやすく始業時刻の時間のセルを始業時、分のセルを始業分と称して説明します。

なるべく時間や分といった単位をつけて説明しますが、かえってややこしかったらすみません。

計算式は3行目、すなわちヘッダー行のすぐ下の行のものを抜き出しています。

拘束時間（時間）

=IF(ISBLANK($D3), "", $C3 - $A3 + IF($C3 - $A3) < 0, 24, 0) - IF($D3 - $B3 < 0 , 1, 0))

最初のISBLANKは空白処理です。終業分が入力されるまでは何も表示しないようにします。

拘束時間は終業時-始業時で計算するのですが、17:55始業・8:05終業という場合はマイナスになってしまうので、その時は24時間をプラスするようIFで分岐させます。

また、終業分-始業分がマイナスの場合は1時間繰り下げます。

拘束時間（分）

=IF(ISBLANK($D3), "", $D3 - $B3 + IF($D3 - $B3 < 0, 60, 0))

分も時間と同じく終業分-始業分で計算するのですが、マイナスになる場合は60分をプラスします。

時間の方でも同様の条件の時は1時間繰り下げているのでこれで帳尻が合うというわけです。

深夜拘束（時間）

=IF(ISBLANK($D3), "", IF(OR($A3 + $E3 >= 22, $A3 < 5), 7 - IF($A3 >= 22, IF($B3 > $D3, $A3 - 21, $A3 - 22), IF($A3 < 5, IF($B3 > 0, $A3 + 3, $A3 + 2), 0)) - IF($C3 < 5, 5 - $C3, 0)), 0))

こんな複雑になろうとは……。できるだけ短い式で済ませたかったのですが、これが僕の限界でした。

最初に始業時と終業時の合計が22時以降、あるいは終業時が5時以前の時は深夜拘束時間があると判定させ、それ以外の時は深夜拘束時は0時間であるとはっきりさせます。

深夜時間は22時から翌朝5時の最長7時間です。この7時間から深夜以外の時間を引いていきます。

始業時が22時以降の場合、始業分が終業分よりも大きければ始業時より21時間、そうでなければ始業時より22時間を引いた数を7時間からマイナスします。

始業時が5時よりも早い場合、始業分が1分でも過ぎていればであれば3時間、0分ジャストであれば2時間を7時間からマイナスします。

就業時が5時以前であれば5時間から就業時を引いた数、そうでない場合は0時間を7時間から引きます。

これで深夜拘束時は計算できます。

深夜拘束（分）

=IF(ISBLANK($D3), "", MOD(IF(OR $A3 >= 22, $A3 < 5), 60 - $B3, 0) + IF(OR $C3 >= 22, $C3 < 5), $D3, 0), 60))

22時〜5時の間に始業している場合は60分から始業分をマイナス…①

22時〜5時の間に終業している場合は終業分…②

①と②を足したものを60で割って、余りが深夜拘束分になります。

深夜実働（時間）

=IF(ISBLANK($D3), "", IF(G3 > J3), J3, G3)) 

拘束時間から休憩時間を引いたものが実働時間となり、深夜実働は深夜拘束とイコールです。

ただし、拘束時間＝深夜拘束といった場合、休憩時間を引いた結果、実働時間よりも深夜実働が上回るという矛盾が生じてしまう可能性があります。

その場合は実働時間が深夜実働とイコールになるようにしています。

基本給与

=IF(ISBLANK($D3), "", $B$6 * J3 + ROUNDUP($B$6 * (K3 - MOD(K3, 15)) / 60, 0))

実働時に時給を掛けるのは簡単ですが、問題は実働分の方。15分刻みで給与が発生するようにしなければなりません。

そこで実働分から、実働分を15で割った余りをマイナス、これを60分で割って時給に掛けます。

小数点が発生する場合は切り上げるようにROUNDUP関数を使います。

深夜手当

=IF(ISBLANK($D3), "", ROUNDUP(($B$6 * L3 + ROUNDUP($B$6 * (M3 - MOD(M3, 15) / 60, 0) * 0.25, 0))

深夜手当は、深夜実働で基本給与同様の計算をした後、0.25（25%）を掛けてROUNDUP関数で切り上げています。

各合計時間（時間）

=SUM(E3:E5) + ROUNDDOWN(SUM(F3:F5) / 60, 0)

時間は素直に合計し、分の合計が60分を超えた分はプラス1時間します。

各合計時間（分）

=MOD(SUM(F3:F5), 60)

60分を超えた分は時間に加算されますので、その余りを求めます。

残りは普通のSUM関数なので何も言うまい。