エクセルで残業時間を計算！1分単位or1時間単位で勤務時間を管理

=IF( B2 >= 80, “合格”, “不合格” )

ポイント
・○以上で条件を指定する場合は、[ >= ]を使用します。
┗〇以下で条件を指定する場合は、[ <= ]を使用します。

・真の場合、偽の場合で返す値がテキストの場合は、[ ” ]で囲ってあげます。

IF関数をさらに振り返りたい方はこちら。
【IF関数・IFS関数】の使い方から具体例までご紹介

=TIME( A2, B2, C2 )

=IF(C2-B2>=TIME(8,0,0),C2-B2-TIME(1,0,0),C2-B2)

条件式の説明：C2-B2>=TIME(8,0,0)
勤務時間を求めるためには、開始時刻と終了時刻の差を計算する必要があります。そのためIF関数の条件式で[ 終了時刻 – 開始時刻 ]で指定をします。休憩時間がない場合の条件式はこのままで大丈夫ですが、8時間以上労働している方には休憩を1時間与えなければなりません。なので、勤務時間が8時間以上の場合は終了時刻 – 開始時刻の差から休憩1時間を引かなければなりません。よって、IF関数の条件式は[ 終了時刻 – 開始時刻 >= 8時間]で指定してあげます。
※8時間をTIME関数のTIME(8,0,0)で指定

真の場合に返す値の説明：C2-B2-TIME(1,0,0)
条件式の[ 終了時刻 – 開始時刻 >= 8時間]が真の場合、休憩1時間を引かなければなりません。なので指定方法は[ 終了時刻 – 開始時刻 – 休憩1時間 ]と入力することができます。
※1時間はTIME関数のTIME(1,0,0)で指定

偽の場合に返す値の説明：C2-B2
条件式の[ 終了時刻 – 開始時刻 >= 8時間]が偽の場合、休憩1時間を与える必要はありません。なので指定方法は休憩1時間を引かずに[ 終了時刻 – 開始時刻 ]と入力することが出来ます。

まずは、日付～終了時刻が記載されている表を作成しました。
下記の表を使って勤務時間表に関数を入力していきます。

通常では、セルの書式設定は標準になっているので、
セルの書式設定を時刻へ変更します。

勤務時間は、8時間以上労働した場合に休憩1時間を与えなければなりません。

半日勤務をしている従業員もいるので、8時間を超えたら勤務時間から休憩1時間を引くかつ、8時間を超えない場合は休憩1時間を引かないという条件を指定しなければなりません。

なので計算式は、[ 終了時刻 – 開始時刻 ] >= 8時間 の場合は、休憩1時間を引いて、条件に当てはまらない場合は、休憩1時間を引かずに[ 終了時刻 – 開始時刻 ]で指定することが出来ます。

=IF(D5-C5>=TIME(8,0,0),D5-C5-TIME(1,0,0),D5-C5)

関数の説明

• 最初にIF関数の条件式で、[ 終了時刻 – 開始時刻 ]が8時間以上なら、[ 終了時刻 – 開始時刻 ]から1時間を引くと指定。
• 偽の場合は、[ 終了時刻 – 開始時刻 ]で計算。

勤務時間が8時間を超えた場合は、残業時間が表示されるように関数を組んでいきましょう。今回、残業時間がない場合は「-」ハイフンで表示したいと思います。

=IF(E5>$A$2,E5-$A$2,”-“)

関数の説明

• 勤務時間が8時間より多ければ[ 勤務時間 – 8時間 ]を引くように指定。
• 条件が偽の場合は、[“-“]ハイフンで返すように指定。

残業時間の中には、深夜残業を含む場合もあります。深夜残業も可視化していきましょう。深夜残業がない方は「-」ハイフンで表示したいと思います。

=IF(D5 > TIME(22,0,0), D5-TIME(22,0,0),”-“)

関数の説明

• 終了時刻が22時より後であれば、[ 終了時刻 – 22:00]で計算。
• 条件が偽の場合は、[“-“]ハイフンで返すように指定。

最後に残業時間から深夜残業を省いていきましょう。深夜残業を求めることが出来れば、こちらは簡単にできます。関数式を見ていきましょう。

=IF(G5=”-“, F5, F5-G5)

関数の説明

• 残業時間がハイフンの場合は、ハイフンを返す（残業時間を返す）ように指定
• ハイフンじゃない場合は、[ 残業時間 – 深夜残業 ]を計算するように指定

ここまでの手順を進めることで勤務時間や残業時間を可視化することが出来ます。完成した表は下記になります。

▼勤務時間を求める流れの振り返り
勤務時間表を作成する条件は下記とします。

1. 日付・名前・開始時刻・終了時刻は入力されているとする
2. 開始時刻から残業時間までのセルの書式設定を時刻に合わせる
3. 8時間以上労働する場合は、1時間の休憩を与える
4. 1日8時間勤務でオーバーした場合は残業時間とする
5. 22時以降の残業は深夜残業とする

それでは勤務時間表を作成していきましょう。

まずは、日付～終了時刻が記載されている表を作成しました。
下記の表を使って勤務時間表に関数を入力していきます。

通常では、セルの書式設定は標準になっているので、
セルの書式設定を時刻へ変更します。

勤務時間は、8時間以上労働した場合に休憩1時間を与えなければなりません。

半日勤務をしている従業員もいるので、8時間を超えたら勤務時間から休憩1時間を引くかつ、8時間を超えない場合は休憩1時間を引かないという条件を指定しなければなりません。

なので計算式は、[ 終了時刻 – 開始時刻 ] >= 8時間 の場合は、休憩1時間を引いて、条件に当てはまらない場合は、休憩1時間を引かずに[ 終了時刻 – 開始時刻 ]で指定することが出来ます。

=IF(D5-C5>=TIME(8,0,0),D5-C5-TIME(1,0,0),D5-C5)

関数の説明

• 最初にIF関数の条件式で、[ 終了時刻 – 開始時刻 ]が8時間以上なら、[ 終了時刻 – 開始時刻 ]から1時間を引くと指定。
• 偽の場合は、[ 終了時刻 – 開始時刻 ]で計算。

ここからは、セルの書式設定を変更していきます。残業時間・深夜残業・深夜残業を省いた残業時間の項目の書式設定を「時刻」から「標準」に戻していきましょう。

勤務時間が8時間を超えた場合は、分単位で残業時間を表示するようにしていきましょう。残業をしていない場合は「-」ハイフンで表示したいと思います。

=IF(E14>$A$2,(E14-$A$2)*24*60,”-“)

関数の説明

• 勤務時間が8時間を超える場合は、[ 勤務時間 – 8時間 ]で計算結果を[ *24*60 ]で計算をする。
• 勤務時間が8時間を超えない場合は[ “-” ]ハイフンで返すように指定。

終了時刻が22時を超えている場合は、深夜残業になります。深夜残業も分単位で計算していきましょう。

=IF(D14 > TIME(22,0,0), (D14-TIME(22,0,0))*24*60,”-“)

関数の説明

• 終了時刻が22時を超える場合は[ 終了時刻 – 22時 ]の計算結果を[ *24*60 ]で計算をする。
• 終了時刻が22時を超えない場合は[ “-” ]ハイフンで返すように指定。

残業時間と深夜残業を求められれば、深夜残業を除いた残業時間を求めることは簡単です。さっそく関数を見ていきましょう。

=IF(G14=”-“, F14, F14-G14)

関数の説明

• 残業時間が「-」ハイフンなら、「-」ハイフンを返すように指定。
• 残業時間が「-」ハイフンではない場合は、[ 残業時間 – 深夜残業 ]を計算するように指定。

ここまでの手順を進めることで残業時間を分単位で計算することが出来ます。完成した勤務時間の表を見ていきましょう！