勤務時間と残業時間について休憩時間を考慮して算出したい | EXCELトピックス

スポンサーリンク
スポンサーリンク
amazon
スマイルSALE
--:--:--
~
ad. 価格範囲を指定して商品を探せます

エクセルで勤務時間と残業時間を算出する方法

Excelを使って、勤務開始時間、終了時間、休憩時間から1日の勤務時間を計算し、所定労働時間(8時間)を超えた分を残業時間として算出する方法を紹介します。日々の勤務時間管理や残業計算に役立つExcelの数式を活用して、正確に勤務時間を計算できます。

先に結論

同じ日の勤務時間は「終了-開始-休憩」で計算し、所定時間を超えた分を別セルで求めます。

基本設定

まず、勤務開始時間、終了時間、休憩時間、勤務時間、残業時間をそれぞれ入力する列を準備します。ここでは、所定労働時間を8時間とし、休憩時間を除いた勤務時間が8時間を超える場合、その超過分を残業時間とします。

例

以下の表で、A列に「開始時間」、B列に「終了時間」、C列に「休憩時間」、D列に「勤務時間」、E列に「残業時間」を表示させる方法について解説します。

A B C D E
1 開始時間 終了時間 休憩時間 勤務時間 残業時間
2 09:00 18:30 1:00 =B2-A2-C2 =IF(D2 > TIME(8, 0, 0), D2-TIME(8, 0, 0), 0)

表の数式はそのセルへの入力例です。Excel上では、数式を確定すると計算結果が表示されます。

※上記は、所定労働時間が8時間の9時から18時30分までの勤務で、1時間の休憩がある例です。

勤務時間の計算方法

勤務時間は、終了時間から開始時間を引き、その間の休憩時間をさらに引くことで計算します。次の数式を使用します。

勤務時間の数式

セルD2に以下の数式を入力します:

数式の例=B2-A2-C2

この数式では、終了時間(B2)から開始時間(A2)を引き、さらに休憩時間(C2)を引くことで勤務時間を算出します。セルの書式を「[h]:mm」に設定することで、勤務時間を時間と分の形式で見やすく表示できます。

残業時間の計算方法

所定労働時間の8時間を超えた時間を残業時間として計算します。勤務時間(D2)が8時間を超えている場合、その超過分をE2に表示するように設定します。

残業時間の数式

セルE2に以下の数式を入力します:

数式の例=IF(D2 > TIME(8, 0, 0), D2-TIME(8, 0, 0), 0)

この数式では、勤務時間(D2)が8時間(TIME(8, 0, 0)で表現)より長い場合、その超過分を計算し、残業時間として表示します。

セルの書式設定 - 計算結果が小数点で表示される場合の対処方法

計算結果を正確に表示するために、勤務時間と残業時間のセルを「[h]:mm」形式に設定します。

  1. 勤務時間や残業時間が表示されているセル範囲(D2:E2)を選択します。
  2. 右クリックし、「セルの書式設定」を選択します。
  3. 「表示形式」タブで「ユーザー定義」を選択し、「[h]:mm」と入力して「OK」をクリックします。

日付を持たない時刻で翌日までの勤務を表す場合は =MOD(B2-A2,1)-C2 とします。ただし24時間以上の勤務は区別できないため、開始・終了に日付を含めて =B2-A2-C2 と計算します。D列・E列の表示形式は [h]:mm にします。この例の残業は、便宜上1日8時間を超えた時間です。給与計算に使う場合は実際の勤務・休憩・集計基準に合わせてください。

まとめ

Excelで勤務時間と残業時間を計算する方法について解説しました。勤務時間は「終了時間 - 開始時間 - 休憩時間」、残業時間は「勤務時間 - 所定労働時間(8時間)」として算出することで、日々の勤務管理が効率的に行えます。数式を活用して、勤務時間の記録を自動化し、管理の手間を減らしましょう。