文字列から時間を取り出しif関数で大小を決めたい

質問

こんにちわ。エクセル2003での質問です。

1列目に1～31日の休憩時間、
2列目に1列目の時間が45分以上経過の場合に×、未満の場合に○を表示させるif関数を作る予定です。
この休憩時間についてですが、別のシートからvlookup関数で呼び出しており、
これが文字列扱いになるため単純に「45分」以上かどうかが作れません。
休憩時間は「:30:30」というふうなエクセルとしては計算できない文字列で表示され、しかも時間、分、秒まで表示されます。
なんとかして「:30:30」というような文字列から、数字（分）を取り出し、大小の比較に使えるようなものに変換できないでしょうか。
なお検索サイトで調べてみましたが、「時刻」のことは載っているのですが今回のような「時間」に関してはうまく見つかりませんでした。エクセルでは自国も時間も同じ考え方なのでしょうか。
変換できる場合は、if関数で使えるように45分がなんという数字に変わるのか、セルの書式はどのようにすればいいのかも、あわせて教えていただけますでしょうか。
45分以外に60分の場合もあわせてお願いします。

回答No.3

keithin · ベストアンサー

>休憩時間は「:30:30」というふうなエクセルとしては計算できない文字列で表示され

Ａ１にその「:30:30」などが計算されているとして
Ｂ１に
=IF((0&A1)-"0:45">=0,"over","under")
=IF((0&A1)-"1:00">=0,"over","under")
などのように。

お礼

この式でうまくいきました！！
＆で０を追加すれば普通に引き算ができるんですね。目からうろこでした
本当にありがとうございます

回答No.4

mu2011

「:30:30」は表示としても異常ですのでこの点を補足説明して改善した方がよいと
思いますが如何でしょうか。
例えば、=TIMEVALUE("0"&VLOOKUP式)で内部シリアル値に変換できますので
45分以上の判定なら、IF(休憩時間>="0:45"*1,"×","○"）で可能になります。

>エクセルでは自国も時間も同じ考え方なのでしょうか。
　⇒同じです。
　　因みに24時間以上を表示したい場合は[h]:mm、60分以上を表示したい場合は[m]で
　　表現します。

お礼

質問が分かりにくくて失礼いたしました。

timevalueの場合は今回のような式になるんですね。
timevalueも思いついたのですが、自分で作った時は（）の中がおかしかったようで
反応しませんでした。
今回は別の方の式を使わせてもらいますが、大変参考になりました。

＞同じです
ありがとうございます！時刻と時間も気になっていたことだったので、すっきりしました。

回答No.2

kagakusuki

>休憩時間は「:30:30」というふうなエクセルとしては計算できない文字列で表示され、しかも時間、分、秒まで表示されます。

　念のため確認したいのですが、「:30:30」とは、0時間30分30秒を表していると考えて宜しいのでしょうか？
　又、1時間30分15秒は「1:30:15」と表示されると考ええて宜しいのでしょうか？
　それと、1列目とはA列の事なのでしょうか？

　もしそうであれば、御質問の関数は次の様なものとなります。

=IF(ISNUMBER(1/((0&INDEX($A:$A,ROW()))-INT((0&INDEX($A:$A,ROW()))+0)>0)),IF((0&INDEX($A:$A,ROW()))-"0:45"<0,"○","×"),"")

　上記の関数を「×○を表示させたいセル」の内の、適当な1つを選んで入力してから、そのセルをコピーして、その列中の他のセルに貼り付けて下さい。

　尚、上記の関数は、セルの参照形式がA1形式（列番号がABC・・・で表示される形式）の場合のもので、もしセルの参照形式が1、2、3・・・の様に数字で表されている形式（R1C1形式）の場合には、御質問の関数は次の様なものとなります。

=IF(ISNUMBER(1/((0&INDEX(C1,ROW()))-INT((0&INDEX(C1,ROW()))+0)>0)),IF((0&INDEX(C1,ROW()))-"0:45"<0,"○","×"),"")

お礼

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

＞念のため確認したいのですが、「:30:30」とは、0時間30分30秒を表していると考えて宜しいのでしょうか？
＞又、1時間30分15秒は「1:30:15」と表示されると考ええて宜しいのでしょうか？
＞それと、1列目とはA列の事なのでしょうか？

すべてそのとおりです。質問分の書き方がわるく申し訳ありませんでした・・・
せっかく時間割いていただいた式なのですが、今回は他の方の式でうまくいきました。
ISNUMBERやINDEXに関して初めて検索し、こんな関数があるんだと大変参考になりました！

回答No.1

noname#146604

vlookup抽出元のデータはどのように入力されているのだろうか。
1列目の抽出方法を変えた方がいいのでは

お礼

vllokup抽出元について、説明不足で大変失礼いたしました。

抽出元のファイルは、時間計測ソフトで測定し、それをエクセルに自動で貼り付けています。
時間計測ソフトの仕様で、例えば「0時間20分20秒」をあらわしたくても「0:20:20」でなく「:20:20」という表示になってしまっています。
私はそのデータをそのままもらっていますので抽出元のファイルは操作できません。

抽出元ファイルは縦軸に各勤務メンバーの名前、
横軸にそれぞれの休憩時間、業務1の時間、業務2の時間・・・と貼り付けられているので、
vlookup関数で抽出元ファイルのうち休憩時間のセルを抜き出しています。