最近、シフト管理表をExcelで作る機会があり、夜勤のような日付を跨ぐ勤務時間の計算で少し苦戦したので、Excelでの計算方法を備忘録として残しておく。

例えば以下のような表があるとする。

セルC2に開始時間と終了時間の差となる時間を入れたいのだが、単純に以下のような数式を入力すると失敗する。

日付を跨ぐ計算となるので、正しくは9:00とならなければいけないのだが、結果は15:00となってしまっている。

日付を跨いだ時間の差を計算する数式

正解はこちら。

終了時間から開始時間を引くことで正しい時間を得ることができるのだが、数式内にあるIF関数のところが重要なポイント。

IF文を使わない場合でも同じ結果を得ることができるように見えるが、

表示形式を変更して実際の値を見ると、以下のように結果は大きく異なることが分かる。

Excelでは1日24時間を「1」として考えるため、セルC2の値「AM9:00」は「9 / 24 = 0.375」となり正しい結果となっているのだが、セルD2は丸1日分の値がずれているのが分かる。

そのため、日付跨ぎの時間計算をおこなう場合は単純な引き算だけでなく、IF文で別途1日分の値を加算してやる必要があるというわけだ。

表示形式が時間表記となっていると一見同じ結果に見えてしまうため、この罠にハマらないように気を付けてもらいたい。