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

回答No.7

MarcoRossiItaly

No.6で一部、誤ったので、訂正します。ごめんなさい。

　「「40980.95833」と表示されるので、1900年1月1日午前0時から40980.95833日後という言い方もできるでしょう。
」
→「……表示されるので、1899年12月31日午前0時から……」

お礼

MarcoRossiItaly様
たびたびのご説明ありがとうございます。

回答No.5

gyouda1114

翌日が前日の時刻より大きくなる
例えば開始が5:10で終了が6:10となることがなければ
次の式で翌日の時間に1を加えて前日の時間を引くで
できます。
N5に　=IF(M5<J5,M5+1-J5,M5-J5)

夜勤のアルバイトの給与計算
http://kokodane.com/tec3_5.htm

お礼

gyouda1114様
ご回答ありがとうございます。
お礼が遅くなり恐れ入ります。
上記のIF文参考にさせていただきます。
また合わせてURLも参考にさせていただきます。

回答No.4

MarcoRossiItaly

No.3さんに賛同します。

No.3さんの式を入力。次に、そのセルの書式を「ユーザー定義　h:mm」などに設定すると、時間として表示されます。

時刻が入力されているセルの書式を試しに「標準」などにしてみると、「22:10」は「0.923611111」と、「1:15」は「0.052083333」と変化します。これは、時刻のシリアル値が表示されている状態です。

このシリアル値は「24時間=1」とする小数です。「0.923611111」とは、「0.923611111日」のことです。ですから、No.3さんの式で「1」を足しているのは、24時間後の時刻に直すという意味です。

「#」が表示されている状態は、負の数に対して時刻などの書式が設定されていることを意味しています。Excelにとってみればエラーではなく、そのように表示することになっているだけです。ですから、単に時刻に足し算をすれば、24時間後に変更され、「#」でない時間が表示されます。

ただ既出の式では、計算結果が例えば「25時間」となるべき場合に、それより24時間少ない「1時間」となってしまう点が気になります。お示しのデータで、答えが24時間以上になるケースはあり得るのですか？

通常、データベースを作る際は、時刻だけでなく日付も記録することが多い気がします。そうであれば日付を式中に導入することで、計算ミスが出ないようにもできそうですが？

お礼

MarcoRossiItaly様
ご回答ありがとうございます。
お礼が遅くなり恐れ入ります。
お時間がございましたら、
補足質問もご確認いただければ幸いです。

補足

MarcoRossiItaly様のご説明について。

＞お示しのデータで、答えが24時間以上になるケースはあり得るのですか？

あまりないとは思いますが、「あり得ない」こともないと考えております。

＞通常、データベースを作る際は、時刻だけでなく日付も記録することが多い気がします。

日付については別のセルに入力するつもりで、
特に関数式の引数にするつもりはありませんでした。

＞そうであれば日付を式中に導入することで、計算ミスが出ないようにもできそうですが？

とすればどのような式が考えられるでしょうか？
もしご存知であればご教示願います。

たびたびの質問で誠に恐れ入りますが
よろしくお願い申し上げます。

回答No.3

Hypnomatic

終了時刻が開始時刻より小さければ、単純に24時間（１を）足すだけです。

=(m5-j5+IF(j5>m5,1))

お礼

Hypnomatic様
ご回答ありがとうございます。
お礼が遅くなり恐れ入ります。
上記のIF文参考にさせていただきます。

回答No.2

tom04

こんばんは！

画像のように##が並ぶ場合は値がマイナス！というコトですので、

N5セル（セルの表示形式は　h:mm )に
=IF(J5>M5,M5+1-J5,M5-J5)
としてみてはどうでしょうか？m(_ _)m

お礼

tom04様
ご回答ありがとうございます。
お礼が遅くなり恐れ入ります。
上記のIF文参考にさせていただきます。

回答No.1

noname#176157

IF文で24を足すのではだめですか。

お礼

jusimatsu様　
ご回答ありがとうございます
お礼が遅くなり恐れ入ります。
アドバイス参考にさせていただきます。