if関数で「20分以上経過」にしるしをつけたい

質問

いつもお世話になっております。
エクセル2003で、以下のように作りましたがif関数がうまく働きません。

B1～AF1に各月１～31日の休憩時間を入力し、B2には遵守しているかの欄にします。
B1が20分を超える場合「×」そうでなければ「○」とB2～AF2に表示したいのです。
A2には基準値として　:20:00　を入力しました。
B2には　　　if(B2>A2,"×","○")　を入力しました。

しかしB1が20分未満「:01:00」等の場合でも「×」と表示されてしまいます。
元々B1の休憩時間は別シートからVLOOKUP関数で呼び出す式が入っていたので
関数を消して直接「:01:00」と入力し、セルの書式をユーザー書式「h:mm:ss」にしたり「時刻」にしたり試しましたが「×」のままです。
A2も同様セルの書式を変えましたが同じです。
時間は「h:mm:ss」の状態で、元ファイルから呼び出され、秒単位の表示なので、
B1はできれば10進数は使わずこの表記の状態にしたいと思います。
またA2の基準時間は、手動で60分にすることもあるので、A2のセルも利用できればと思います。

ネットで検索すると「時間は大小比較ができない」とあったのですが、
ここから先どうすればいいか行き詰っています
別の関数を3行目以降に追加しても構いません。
何かいい方法がないでしょうか。

よろしくお願いします。

回答No.1

keithin · ベストアンサー

＞A2には基準値として　:20:00　を入力しました。

まず基本事項として，「：２０：００」や「：０１：０１」といった時刻の記入の仕方はありません。
時刻や時間数を記入するときは，「０：２０」（ゼロ時間２０分）や「０：０１：００」（ゼロ時間１分００秒）のように記入します。

手順:
Ａ２の書式設定を時刻にする
Ａ２に改めて
０：２０　　（２０分のことです）
あるいは
１：００　　（６０分のことです。もちろん）
を記入する

１行目を時刻の書式設定にする
Ｂ１に
０：１０
を必ず改めて記入する
Ｂ２に
=IF(B1>$A2,"×","○")　
と式を記入し，どっちが大きければ○や×なのか再確認してから結果を確認する。

さらに
１行目に元の数式を記入し，値を参照してくる
その状態で１行目のセルの書式設定を「標準」に戻し，「正しい時間の数値」がキチンと計算されているか再確認する

＃時間の数字とは
たとえば０：２０と記入したセルに「標準」の書式設定を付け直すと
0.013888889
という値が現れます。これが正しい時間の数値です。
もしもこの操作をしたときに関数の表示が例えば「：２０：００」などのように現れたとしたら，計算式で参照した元のデータから間違っていますので，元からキチンと直します。

正しく時刻の値で引っ張ってこれていることが確認できたら，１行目を時間の表示に設定し直します。

お礼

回答ありがとうございました。

1ステップずつ修正方法を記載いただきありがとうございました。
実は最初「20:00」で試したのですがうまく反応せず、
B1～にてvlookup関数がひっぱってきた数値が「:20:00」のような表記だったので、
「:20:00」という表記でないのが間違いなのかと思っていました
もう一度最初からご指摘の方法で修正してみます。
ありがとうございました。

回答No.2

KURUMITO

二つの大きな間違いをしていますね。
1つは時間を入力する場合には20分でしたら0:20:00のように入力することです。お示しの場合は:20:00のように入力していますがそれではパソコンでは時間として認識していません。時間はパソコンでは数値として認識されなければなりませんが:20:00のような入力では文字列として認識されて、その後に表示形式を変えるといった操作をしても全く意味がありません。
二つにはIF関数の式でif(B2>A2,"×","○")　とB2セルに入力していますが、B2セルに入力するのにB2を式に入れることはできません。=IF(B$1>$A2,"×","○")の間違いでしょう。
Aに$を付けているのはB2セルに入力した式を横方向にオートフィルドラッグしても$A2の表示が変わらないようにするためです。B$1としているのは下の方向にオートフィルドラッグ操作をしてもB$1が変わらないようにするためです。
ところでどうしても現在のままのデータでお望みのようなことをしたいということでしたらB2セルには次の式を入力して右横方向にオートフィルドラッグします。

=IF(OR(B$1="",$A2=""),"",IF(TIMEVALUE(0&B$1)>TIMEVALUE(0&$A2),"×","○"))

お礼

回答ありがとうございます。

＃１の方にも書いたのですが、
実は最初「20:00」で試したのですがうまく反応せず、
B1～にてvlookup関数がひっぱってきた数値が「:20:00」のような表記だったので、
「:20:00」という表記でないのが間違いなのかと思っていました

あとご指摘のとおり、B2にいれた式は。=IF(B1>$A2,"×","○")です。
このサイトで記載する時に間違いました。質問をややこしくしてすみません。
最後に書いていただいた式で一度試してみたいと思います。ありがとうございました。