Excel。午前0時をまたぐ時間計算は、減算だけでは算出できません。
＜IF関数＞
夜間勤務など午前0時をまたぐ時間計算は、単純に減算だけでは、算出することができません。
D列の勤務時間は、C列の退社からB列の出社の時間を減算すれば、算出することができるはずです。
D4には、
=C4-B4
と設定してあって、オートフィルで数式をコピーしています。
D4は、きちんと減算してくれていて「3:00」と結果を表示していますが、D5やD6は「####」と表示されてしまっています。
D5をアクティブにすると、次のメッセージが表示されます。
「負であるか、大きすぎる日付および時間は######と表示されます。」
どうやら、Excelは午前0時をまたぐ場合、単純な減算だけでは算出できないようです。
この原因は、Excelでは、時間や日付は、シリアル値で管理されているためです。
よって、D4の数式をIF関数で修正します。
=IF(B4<C4,C4-B4,C4+1-B4)
あとは、オートフィルで数式をコピーします。
このように、午前0時をまたいでも、減算結果を表示することができました。
退社が「5:00」とあれば、翌日の「5:00」というイメージをもちますが、Excelは単純に「5:00」という時間でしかありません。
出社の「22:00」と退社の「5:00」は1900/1/1の一日内の数値の大小でしかないということです。
それを踏まえて、数式をつくります。
IF関数の数式を確認します。
最初の引数の「論理式」は、B4<C4。
退社のほうが出社よりも大きいかを判断させます。
これで、退社が午前0時以降なのかを判断することができます。
2つ目の引数の「真の場合」は、C4-B4。
普通の退社-出社ですね。
3つ目の引数の「偽の場合」は、C4+1-B4。
なぜ「+1」しているのかというと、翌日とさせたいわけですね。
Excelは1日をシリアル値で「1」としています。
「5:00」は29:00ですね。
なので、「+24」したいわけです。
ただ単に「+24」すると、24日後ということになってしまいます。
シリアル値で考えると、「1日＝1＝24:00」なので、「+1」するということは「24:00」を足して24時間以上の表示にしているわけです。
こうなれば、算出結果が「負」にはなりません。
「######」と表示されないで、勤務時間を算出することができたというわけですね。
Excelの時間計算は、シリアル値を考えないといけないケースがあるので、注意が必要ですね。
