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

以前にMac用の表計算ソフトNumbersで給与計算の方法を取り上げました。当時は「Excelを使えばもっと簡単にできるのになぁ」という感想でした。

ちょいと仕事で給与計算に関することをやらないといけなさそうで、その予行練習でExcelで作ってみたのですがそこそこ大変でした。

サンプル

サンプル

https://www.iehohs.com/ftp/excel-attendance-list.xlsx

こちらがデータです。

黄色のところを入力し、あとはすべて埋め込み関数で計算されます。

要件

Numbersの時と同じ要件で作成しました。

22:00～5:00の間は25%の深夜手当がつく
15分未満で切捨て
給与は小数点以下切り上げ

Numberと違うのは時間と分でセルを分ける必要がなく、１つのセルに時刻のデータ型で入力して計算が可能です。

深夜拘束

=IF(D4="","", IF(OR(C4+E4>=TIME(22,0,0), C4<TIME(5,0,0)), IF(D4>C4,IF(D4<$F$2,MIN(D4,$H$2)-MAX(C4,"0:00"),MIN(D4,"23:59")-MAX(C4,$F$2)),MIN(D4+"24:00",$H$2+"24:00")-MAX(C4,$F$2)),0))

やはりややこしいのは深夜拘束。日をまたぐ場合、5:00であれば29:00と24時間プラスですればいいらしいのですが、29:00と入力するのは運用上難しいです。

最初の「IF(D4=””, “”,」は空白処理です。終業時刻が入力されるまで空白です。

「IF(OR(C4+E4>=TIME(22,0,0), C4<TIME(5,0,0)),」は、まず深夜時間に入っているかを判定します。始業時刻に拘束時間を足して22:00を超えるか、始業時刻が5:00より前なら深夜時間が含まれていると判定、それ以外なら0を返します。

「IF(D4>C4,IF(D4<$F$2,MIN(D4,$H$2)-MAX(C4,”0:00″),MIN(D4,”23:59″)-MAX(C4,$F$2))」では、まず終業時刻が始業時刻より大きい場合の処理です。

D4が22:00より小さい場合、D4か5:00の小さい方（MIN関数）から、C4か0:00の大きい方（MAX関数）を引いた時間を入力。D4が22:00より大きい場合、D4か23:59の小さい方から、C4か22:00の大きい方を引いた時間を返します。

逆に始業時刻が終業時刻より大きい場合、これは日をまたいでいる場合です。D4に24:00を足した時間か5:00に24:00を足した時間の小さい方から、C4と22:00の大きい方を引いた時間を返します。

深夜実働

=IF(D4="","",IF(E4=F4,H4,F4))

深夜実働は基本的に深夜拘束と同じ時間になるのですが、拘束時間から休憩時間を引いた実働時間より少ない場合は実働時間とイコールになるようにします。

基本給与

=IF(D4="", "", ROUNDUP($D$2*FLOOR(H4, $L$2)*24, 0))

実働時間の15分未満は切捨てとなります。FLOOR関数を使って0:15で切捨て、その後24を掛けて1:30であれば1.5といったような時給をかけられる状態にします。

深夜であればこれに25%をかけて手当とします。ROUNDUPで小数点以下は切り上げます。

という感じでできました。Numbersのときほどではありませんが、そこそこめんどくさいですね。

とりあえずMIN関数とMAX関数でこんな使い方ができるのは知りませんでした。勉強になりました。