【Excel】エクセルで残業時間の合計を計算するには

この記事を読むのに必要な時間は約 6 分です。

 

 

残業時間を計算したい。

 

やり方は次の手順です。

  1. 残業時間の項目を用意する
  2. 24時間を1として、1日毎の残業時間を算出する(8時間なら勤務時間-(8/24)
  3. SUM関数で合計する
  4. セルの書式設定で、ユーザー定義を[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時間にされてしまってはたまりませんから。
合計時間がなんかオカシイと思ったら、書式設定を見直してみてくださいね。

 

最後までお読みいただき、ありがとうございました。