休憩を「分」で入力できて夜勤にも対応する時間計算

開始時刻と終了時刻、休憩時間を入力して勤務した時間を算出する計算について、2 つ書きます。下図の [勤務時間] 列に数式を作成して、[終了] から [開始] を引いて、さらに [休憩] を引いた結果を表示します。

ここでは、[休憩] については「60 (分)」や「45 (分)」と入力したいと考えています。

わかりやすいように、[開始] と [終了] と [勤務時間] の列のセルには、「[h]:mm」の表示形式 (翌 1:00 を 25:00 と表示したいときに使う表示形式) を設定してから数式を作ります。

休憩時間を「分」で入力する

[休憩] 列の値が「60」などの数値のとき、終了時刻から開始時刻と休憩を引き算すると、「######」のように表示され、正しい結果が表示されません。

これは、[勤務時間] 列に計算結果として「-59.625」などのマイナスの値が入るけれど、「[h]:mm」の時刻の表示形式だと表現しきれないため、エラーになっている状態です。

「######」の表示を回避するために、数式をちょっと工夫します。

Excel は 1 日を「1」というシリアル値で表します。12:00 は「12 時」で「0.5」、6:00 は「6 時」で「0.25」です。そのため、1 日を「分」で表すと、「1440 分」で、「1 分」は「1/1440 日」ということになります。

ということで、[休憩] に入っている「60」などの数値を 1440 で割って、シリアル値 (0.041667) にしてから引き算するとうまくいきます。

セルをコピーして、休憩が 45 分の場合の勤務時間も計算できていることを確認します。

これで、休憩を「分」で入力したい、は解決。

夜勤にも対応した時間計算をする

先ほどの数式で計算する場合、[終了] が翌日となった場合に、「#####」のエラー表示になってしまいます。

これは、[開始] の「22:00」はシリアル値だと「0.9167」で、[終了] の「7:00」はシリアル値だと「0.2917」なので、[終了] から [開始] を引いた時点でマイナスの値になり、「[h]:mm」の時刻の表示形式だと表現しきれないために起きている現象です。

これを解決する方法はいくつかありますが、ここではよく使われる IF 関数を使って分岐処理をして対応します。開始が「9:00」で、終了が「18:00」なら普通に [終了] － [開始] でよいけれど、[終了] の時刻が [開始] の時刻よりもはやい (日をまたいでいる) 場合は [終了] に＋ 1 日する、という数式で処理します。

「C3>=B3」が [終了] が [開始] 以降の時刻かどうかをチェックする論理式、「(C3-B3)-(D3/1440)」は、論理式の結果が TRUE ( [終了] が [開始] 以降) のときの処理で、単純な引き算。

「(C3+1-B3)-(D3/1440)」は、論理式の結果が FALSE ( [終了] が [開始] より前) の時の処理で、[終了] の時刻に「1」足して＋ 1 日しています。