Excel2007関数 日をまたぐ時刻の計算

質問

• 時間を計測する場合、一方の日付が日をまたいだ場合どのような関数を配置すれば時間のみを計測することができるでしょうか？ご存知の方ご教示願います。
• 現在生活時間の実態調査をするため、データベース形式で活動内容及び時間のデータを収集しようと考えています。ところが、「開始時刻」－「終了時刻」の差の時間を求めたときに、「終了時刻」が翌日の「1：00」など日をまたいだ場合に差を求められず#マークが表示されます。
• #マークを表示させず時間のみを計測させるにはどのように関数式を記述させれば良いでしょうか？（添付画像の関数式）J5：=TIME(H5,I5,) M5：=TIME(K5,L5,) N5：=M5-J5

回答No.6

MarcoRossiItaly · ベストアンサー

日付の導入について、ご質問にお答えします。

日付にも、時刻と同様、シリアル値が振られています。1900年1月1日（日）を1とし、その翌日が2、翌々日が3、…となっている自然数の数列です。

例えば、今日、セルに「3/12」と入力すると、Excelはこれを勝手に今年の日付だと判断してくれます。そのセルの書式によって、「日付」の書式「H24.3.12」とか、「ユーザー定義」の書式「3月12日」など様々に表示されます。入力後のセルにカーソルを置いていると、数式バーに「2012.3.12」などと表示されているので、数式バー内をクリックすることで他の年に編集することも可能です。

さて、2012年3月12日が入力されているセルの書式を「標準」にした場合、「40980」と表示されます。これがこの日のシリアル値です。つまり、1900年1月1日から数え始めて40,980日目であることを意味します。明日のシリアル値は1増えるので「40981」となります。

以上の説明と、「時刻は1日を1とする小数がシリアル値」ということを併せて考えると、どうしたらいいか見えてきたのではないでしょうか？例えば「2012年3月12日の23時」を1つの数で表すことができるのではないでしょうか？そう、足し算をすればいいですね？もしA1に「3/12」が、D1に「23:00」と入力されているならば、E1に「=a1+d1」と入力すれば、この瞬間の値が求められます。ただ、入力直後はE1の書式が時刻などに自動設定されたりするので、シリアル値を見るためには「標準」などに変更してあげましょう。「40980.95833」と表示されるので、1900年1月1日午前0時から40980.95833日後という言い方もできるでしょう。

あとは簡単ですね？上のようにして求めた2つの時点を表す小数どうしで、引き算するだけです。引き算を計算するセルに「時刻」の書式を設定しておけば、何時間何分という具合に表示されます。

また、この方法では、IFによる場合分けは不要で、とにかく引けばいいということになります。24時間でも48時間でも、1年後でも問題ありません。働きすぎにご注意（笑）

それから、日付の入力の仕方が上とは異なっている場合も説明しておきます。A1、B1、C1の3つのセルに「2012」、「3」、「12」と入力されていることにより2012年3月12日を表している場合です。その場合、DATE関数というのを使うと、日付のシリアル値に変換できます。

「=date(a1,b1,c1)」あるいは「=date(2012,3,12)」とセルに入力し、そのセルの書式を「日付」などにします。「標準」ではやはり「40980」と表示されます。D1とH1に時刻が入力されている場合なら、「=date(e1,f1,g1)+h1-date(a1,b1,c1)-d1」などと計算してもいいですね。

※Excelでは、文字列以外の式の入力において、大文字と小文字を区別する必要はありません。

お礼

MarcoRossiItaly様
かなりご丁寧な説明ありがとうございます。
特に「シリアル値」のご解説がよく分かりました。
アドバイス参考にさせていただきます。
助かります。