Excelで勤務時間を計算する式について

質問

会社の勤務報告書が下記画像の形式で手入力しております。

今回、勤務時間を関数を用いた式で算出できないかと思い、
頭を悩ませております。

悩んでいるポイントは、
　・開始時間と終了時間が数値になっている
　・終了時間が四捨五入で記述されている（厳密には６分単位）
という点です。

休憩時間は12:00～13:00と17:40～18:10となっておりますが、
こちらはIF文を用いて処理できそうです。

算数や数学が得意な方や、勤務表をすでに関数化してらっしゃる方、
どうかお力添え頂けないでしょうか。

よろしくお願いいたします。

回答No.3

MarcoRossiItaly

できれば、Excel における通常の時刻の入力方法を採用することをお勧めしたいと思います。次のようにします。

「900」が午前 9 時を表すのであれば、「9:00」とタイプします。これは、確定と同時に午前 9 時の「シリアル値」に変換されてセルに入力され、その値は「0.375」という数値なのですが、見かけは「9:00」と表示されています。「0.375」をセルの書式でそのように見せているだけなのです。

「AM」や「PM」はタイプしなくて大丈夫です。「17:40」と打てば、数式バーでは自動的に「5:40:00 PM」と表示されます。

つまり、「9:00」とタイプした後に、「900」に見えるような書式に変更してあげればいいですね。具体的には、B・C 列のデータ入力したセル範囲を選択して、右クリックなどから「セルの書式設定＞表示形式タブ＞ユーザー定義＞種類ボックス」に「hmm」と入力するだけです。見かけは「900」ですが実態は「0.375」であることが、書式を「標準」などに変更すれば確認できます。

こうしておくと何がいいのかというと、時間の計算がとても簡単になるということです。

シリアル値は、24 時間で「1」になるような値に定められています。1 × 24 = 24 であるのと同様に、0.375 × 24 = 9 が成り立ちます。つまりシリアル値に 24 をかけると、時間数に変換されるのです。したがって、

=24*(c2-b2)

※「24 倍する」代わりに「1:00 のシリアル値で割る」という計算をしても、
　同じ結果が得られます。「=(c2-b2)/"1:00"」という具合に。

というふうに時間差が計算できます。ここまで、関数は不要ということです。

ただ、これだけだと休憩時間が考慮されてませんね。ある期間と他の期間との「重なり」は、「MAX(,MIN-MAX)」という形の数式で表されます。

=24*(c2-b2-max(,min(c2,"13:00")-max(b2,"12:00"))-max(,min(c2,"18:10")-max(b2,"17:40")))

D 列にこの数式を入力したら、そのセルの書式は、上でユーザー定義の時刻の書式を設定したのと同様にして、ボックスに「0.0」と入力するなどしてください。時刻の書式だと、「7.66…」が「16:00」とか表示されてしまったりします。なお D 列が「標準」の書式の場合は、B 列や C 列を編集していると、その右の D 列も書式が自動変更されてしまったりしますので、ご注意ください。

お礼

ご回答ありがとうございました。
確かにきちんと時刻の形式で入力されるべきものですが、
現状全員が図の形式で入力しており、
浸透させるには少し時間がかかると思われますが、
検討はしたいと思います。