日付をまたぐ時間計算について

質問

いろいろ質問がありますが、自分のほしいものと一致する回答が見つけられなかったので教えてください。

エクセルで日付をまたいだ場合の時間計算（引き算）をします。
あるセル(A1)に"1"と入れるとB1にはvlookupで"1"を参照して、予定時刻を表示させます。D1には実際の時刻を入力し、それが予定よりも遅れていればE1に遅れた時間を計算させ(=D1-B1)、遅れがない場合は空白を返します。

予定時刻(B1)が23:30、実際(C1)は翌日00:30の場合
=C1-B1を入れるとD1は-23:00となりました。1時間の遅れなのでこのケースは1:00と表示させたいのですがどうしたらいいでしょうか。
vlookupで参照する元の予定時刻というのは時間だけで日付はありません。（飛行機や電車の時刻表をイメージするとわかりやすいかと思います。日付に関係なく毎日同じ時刻になっている、それと同じです。）
C1にはMM/DD_HH/MM形式で入力することもできますが、B1がHH/MM形式だから引き算ができないのでしょうか。B1,C1はそれぞれこの形式のままにしておきたいです。うまく説明できずにすみませんが、アドバイスよろしくお願いします。

回答No.3

Cupper · ベストアンサー

日付と時間はシリアル値として扱われているので、他の方が回答しているように時間だけを記入すると同じ日として扱われます。
シリアル値は一日で1、二日で2、というように1900年1月1日0時からのカウント値です。

時刻表ということですので、時間差が24時間以内であると仮定して
こんなのは如何でしょう。

　=C1-B1
　　 ↓
　=MOD(C1+"24:00"-B1,"24:00")
　　または
　=MOD(C1+1-B1,1)

明らかにC1セルの時間がB1の時間より後になるのであれば、これで計算できます。
※MOD関数の詳細はExcelのヘルプを参照してください。

24時間以上の差を求めるのであれば素直に日付まで指定して計算することをお奨めします。
D1のセルの表示形式は「 [d] hh:mm 」で良いでしょう。
4日後を指定するのであれば次の式でも可能です。
E1セル=4
　=C1+E1-B1

補足

MOD関数を使うのはまったく思いつきませんでした。その手も使えそうですね。ただ、24時間以上遅れるケースもあり得るのですが、この場合はどうすればいいでしょうか。

>D1のセルの表示形式は「 [d] hh:mm 」で良いでしょう。
この形式で設定するとエラーになってしましました。

回答No.4

grumpy_the_dwarf

B1に別表から定時を引っ張って来ているのはわかりますが、ここに
日付がない限り、何日遅れたかの計算は絶対に出来ません。

2007年4月18日の第1便で入荷するはずのものが同年4月20日の19:30
に届いたとして、どこか別のセルでいいですから「2007年4月18日」
が書いてありませんかね。それを使って、

B1: =日付のセル+vlookupで時刻を持ってくる

とすれば、C1に日付と時刻を書くことで何日遅れでもちゃんと計算
してくれるはずです。もちろん直接引き算しちゃうとC1に日付を省
略出来なくなるので、=C1+if(C1<1,日付のセル,0)-B1くらいの工夫
が必要ですね。

お礼

ありがとうございました。みなさんのアドバイスをもとに、なんとか完成させることができました。

回答No.2

pkh4989

片方だけでも時間だけでは、何日遅れたのか判断出来ないので、
予定時刻(B1)と、実際(C1)の時刻入力を「mm/dd hh:mm」形式で入力した方がいいと思いますよ。

例えば、
セル(B1) -> 04/16 23:30
セル(C1) -> 04/17 00:30
セル(D1)は「hh:mm」形式 -> =IF(C1-B1<=0,"",C1-B1)

にするとセル(D1)に「1:00」が表示されます(遅れがない時は空白)。如何でしょうか。

お礼

ありがとうございます。
両方mm/dd hh:mmにすればおっしゃるとおりきちんと計算されますが、セルB1にはvlookupで別の表（時刻データのみ）を参照しているので日付は入れることができません。ともかく、アドバイスありがとうございました！

回答No.1

grumpy_the_dwarf

B1のように日付が欠落している場合、内部的には1904年1月1日(Mac)
もしくは1900年1月0日(Win)として処理しますので、C1に実際の日付
を入れて引き算しちゃうとエラいことになります。

とはいえ、時刻だけの情報がある条件下では翌日であると主張する
のは面倒くさいので、基本的には、C1に「24:30」と記入します。テ
レビ欄とかでよく見るアレです。書式は「[hh]:mm」にしとくと判り
やすいでしょう。

補足

ありがとうございます。
時刻だけを入れてC1-B1を計算させると同じ日とみなされるのか、遅れはないことになっています。遅れをきちんと計算させるにはどうすればいいのでしょうか。
また、遅れた時間を自動的に出すほうが目的なので、アドバイスいただいた「24:30」という方法とは別のものがあれば教えていただけませんか。たとえば実際の到着が「4日後の13:55」といった場合、「04/21 13:55」として入れれば遅れが計算されるようにしたいです。