
この記事を読むのに必要な時間は約 6 分です。
残業時間を計算したい。
やり方は次の手順です。
- 残業時間の項目を用意する
- 24時間を1として、1日毎の残業時間を算出する(8時間なら勤務時間-(8/24))
- SUM関数で合計する
- セルの書式設定で、ユーザー定義を[h]:mmにする
いまいち分からない場合には、画像で解説したので参考にしてみてください。
目次(リンク)
1日毎の残業時間を計算するには
まずは1日毎の残業時間を算出します。
日毎の勤務時間から、定時の勤務時間を引く
1日の中で基準になる勤務時間があるはずです。
9時から18時まで、1時間休憩の8時間勤務だったり、8時半から17時までだったりと、会社や部署によってさまざまです。
なので、実際の1日の勤務時間から8時間や7.5時間を引けば、残業時間を算出できます。
数式上で8時間を表現すると、8:00ではありません。
1を24時間として計算されるので、8時間なら8/24、7.5時間なら7.5/24となります。
数式は、次のようになります。
=H7-(8/24)
定時退社の日や休日の時間が困る
残業がない日は、どのように表示したいでしょうか。
0:00の表示で問題なければそれでいいのですが、空欄にしたい場合もあると思います。
さらに休日はというと、勤務時間が入っていないので、「#########」のような表示になってしまいます。
イレギュラーな日は、次のように対処します。
残業時間のない日や休日は、IF文で対処する
残業時間が発生するのは、勤務時間が8時間を超える日しかありません。
8時間までは空欄にして、8時間を超える場合に残業時間を表示する数式を書けば解決です。
~の場合A、それ以外はB のような数式はIF文を使います。
H9>(8/24)がTRUEになる場合、H9-(8/24)の結果を表示して、それ以外はブランク””にするわけです。
数式は、=IF(H9>(8/24),H9-(8/24),””)
残業時間のない日が空欄になりました。
休日で出勤していない日も、空欄になりました。
1か月の残業の合計時間を計算するには
1日毎の残業時間は算出出来ました。
あとは合計するだけですね。
SUM関数で合計する
合計するには、言わずと知れたSUM関数。
数式の説明を見ると、SUM(数値1,[数値2], …) のように書いてありますが、これを真に受けてはいけません!
カッコ内は範囲で指定し、=SUM(I6:I36)のように書きます。
残業時間合計が、15:00となりました。
24時間に満たなければこれで問題ありません。
しかし24時間どころか、100時間を超えることも珍しくないのが日本の世の中です。
下図の場合25時間なのですが、24時間を超えると繰り上がって1:00と表示されてしまいます。
対処法は、セルの書式設定です。
セルの書式設定で、24時間以上も表示できるようにする
右クリックなどでセルの書式設定を開きます。
分類をユーザー定義にして、種類のところを直接書きます。
[h]:mmのように、半角の[]でhを囲みます。
サンプルのところに、25:00と表示されました。
OKボタンを押下です。
25:00と表示されました。
まとめ:残業時間の合計は、SUM関数とセルの書式設定に注意
1日毎の残業時間は、実際の勤務時間から、定時の勤務時間を引いた時間になります。
数式に、8:00や7:30とは書けないので、どのように表現するのか紹介しました。
合計の時間は単純にSUM関数で算出できますが、そのままでは24時間以上を正しく表示できません。
書式設定のちょっとしたコツを使えば、30時間だろうが100時間だろうがちゃんと表示されます。
せっかく残業したのに、25時間を1時間にされてしまってはたまりませんから。
合計時間がなんかオカシイと思ったら、書式設定を見直してみてくださいね。
最後までお読みいただき、ありがとうございました。