時間の差分を関数を使ってできないでしょうか。

質問

関数を教えてください。　Excel2010です。

-------------------------------------------------------------------------

Sheet2　：A1～D1のみの表。パターンは色々有る。
　　　　　　　　　　A 　　　　 B 　　　　　 C 　　 　　D
　　　　　　　　収穫日　収穫時間　入荷日　入荷時間
1　パターン1　　10/1　 10:00　 10/1　 12:00

　パターン2　　10/1　 10:00　 10/1　 18:00
　 パターン3　　10/1　 10:00　 10/8　 12:00
　 パターン4　　10/1　 10:00　 10/8　 18:00
　　　・
　　　・
　　　・
-------------------------------------------------------------------------

Sheet1

　　A
1
2

-------------------------------------------------------------------------

≪やりたいこと≫

Sheet1の　A１、A2の関数を下記条件で作りたい！

１つめ
Sheet1のA1では
　Sheet2のA(収穫日)、C(入荷日)を見て【同日】かつ、
　B(収穫時間)、D(入荷時間）をみて、3時間以内なら"1"を表示。
　いずれかでも当てはまらなければ空白。

Sheet2がパターン1の時、Sheet1のA1では
　結果：1　が表示される。

Sheet2がパターン2の時、Sheet1のA1では
　結果：空白　となる。

Sheet2がパターン3の時、Sheet1のA1では
　結果：空白　となる。

Sheet2がパターン4の時、Sheet1のA1では
　結果：空白　となる。

２つめ
Sheet1のA2では
　Sheet2のA(収穫日)、C(入荷日)を見て【別日】または【同日】でも、
　B(収穫時間)、D(入荷時間）をみて、3時間より長ければ"1"を表示。

Sheet2がパターン1の時、Sheet1のA2では
　結果：空白　となる

Sheet2がパターン2の時、Sheet1のA2では
　結果：1　が表示される。

Sheet2がパターン3の時、Sheet1のA2では
　結果：1　が表示される。

Sheet2がパターン4の時、Sheet1のA2では
　結果：1　が表示される。

希望結果--------------------------------------

表）sheet1　：パターン1
　　A
1　 1
2

表）sheet1　：パターン2
　　A
1　
2　 1

表）sheet1　：パターン3
　　A
1　
2　 1

表）sheet1　：パターン4
　　A
1　
2　 1

----------------------------------------------

自分で作った関数は下記だったのですが、どんなパターンでも"1"が立ってしまいます。
A1=IF(sheet2!$A$1=sheet2!$C$1,IF(((sheet2!$D$1)-(sheet2!$B$1)<=3),1,""),"")
A2については作れませんでした…。

時間の差分による関数なのですが、sheet2のB列、D列の時間投入されている
セルの表示形式に指定はありますか？

当初文字列だったので、時刻や数値に変更してみましたが、
特に結果に変わりはありませんでした。

どなたかご教授下さい。
よろしくお願いします。

※上記の説明の表、何度修正しても空白などが入ってしまいうまく表示ができませんでした。
見づらくて申し訳ありません。

回答No.3

bunjii

>時間の差分を関数を使ってできないでしょうか。 時間の差分を関数を使ってできないでしょうか。
Excelで日時の計算をするときは対象のセルに入力されている値を正しく理解しないといけません。
日付の値は西暦1900年1月1日を1とする経過日数です。
時刻の値は1日を24で除した値が1時間として0時～翌日の0時までを小数で表しています。
24時=1 → 翌日0時

>自分で作った関数は下記だったのですが、どんなパターンでも"1"が立ってしまいます。
> A1=IF(sheet2!$A$1=sheet2!$C$1,IF(((sheet2!$D$1)-(sheet2!$B$1)<=3),1,""),"")
前項で日時の値を理解できればあなたの数式の何処に誤りがあるか分かるでしょう。
内側のIF関数の論理式で「((sheet2!$D$1)-(sheet2!$B$1)<=3)」は入庫時刻D1から収穫時刻B1を減じた値は1未満なので比較する3時間は3/24でなければなりません。
あなたの数式では72時間（3日）と比較していますので全てTUREとなり"1"が立つのは当然の結果です。
尚、今回の例ではIF関数を入れ子にする必要はありません。
A1=IF((Sheet2!C1+Sheet2!D1)-(Sheet2!A1+Sheet2!B1)<=3/24,1,"")

回答No.2

keithin

先にエクセルの操作上の注意点から。

＞当初文字列だったので、時刻や数値に変更してみました

非常にたくさんの初心者さんが陥る間違いですが、「書式を変更しました」だけでは、変更できていません。
書式をどーこーしたい時は、基本は一回セルをＷクリックしてからＥｎｔｅｒを押して、「データを再入力する」事を忘れず行ってください。

＃エクセルの操作に慣れてくると、もっと効率的な操作もあります。

準備：正しいデータの入力
シート２のＡ１：Ｄ１を選び、セルの書式設定の表示形式を「標準」に変える
その際に「変な数字」が現れたら、そのセルはＤｅｌｅｔｅキーで消してから、改めて日付、時刻を入力する
そうでなくてもＡ１、Ｂ１、Ｃ１、Ｄ１をそれぞれ一つずつＷクリックしてはＥｎｔｅｒし、データを再入力する

手順：
シート１のＡ１には
=IF(AND(Sheet2!A1=Sheet2!C1,Sheet2!D1-Sheet2!B1<=TIME(3,0,0)),1,0)
と記入する

シート１のＡ２には
=IF(Sheet2!C1+Sheet2!D1-SUM(Sheet2!A1:B1)-"3:0">0,1,0)
と記入する

＃いくつかの計算パターンを組み合わせて掲示しています。適宜応用してください。

回答No.1

shintaro-2

1日が数値としては１なので、関数で計算する場合3hなら24分の３です