エクセルにて負の時間を0:00と表示する方法を教えてください

タイトルのとおり、負の時間になった場合に0:00と表示したいのですが、なかなかうまくいきません。

エクセルにて勤務の遅刻・早退の表を作っています。8:30までにこなかった場合に超過時間を表示し(9:00にきた場合には0:30)、17:00より前に帰った場合に過不足時間を表示、という表を作っています。

http://oshiete1.watch.impress.co.jp/qa4183597.html
この過去の質問を参考にやってみましたが・・・うまくいきません；

現在、A1にある元データの数字（例：815)を拾う為に、セルのA3に
=IF(A1="","",TEXT(A1,"0!:00"))
という数式が入れてあります。
A4のセルには
=IF(ISBLANK(A3),"",IF(A3>TIMEVALUE("8:30"),A3-"8:30",0))
という数式が入れてあります。

A4のセルに負の時間であれば0:00と表示、という関数を入れれば出来そうなのですが、試行錯誤してもわかりませんでした。

ご教示いただけたらと思い投稿しました。
宜しくお願いします。

No.3 ベストアンサー 回答者： grumpy_the_dwarf 回答日時： 2009/11/18 17:09

> =IF(A1="","",TEXT(A1,"0!:00"))

これは時刻じゃなくてただの文字列ですね。これを

> =IF(ISBLANK(A3),"",IF(A3>TIMEVALUE("8:30"),A3-"8:30",0))
この式に入れたら、「A3>TIMEVALUE("8:30")」は常に真です。文字
列は数値より大きいことになってますから。四則演算では勝手に型
を合わせるExcelも、比較演算子では型を意識する必要があります。
というわけでA3を

=if(A1="","",timevalue(text(A1,"0!:00")))
として比較する値同士の型を合わせて下さい。

この回答へのお礼

>> =IF(A1="","",TEXT(A1,"0!:00"))
>これは時刻じゃなくてただの文字列ですね。これを

これだと時刻として扱われず、文字列を時刻のように表示している・・ということであってますでしょうか？

こちらの式でも出来ました！こんな短くてＯＫだったとは目から鱗です。ありがとうございます。今後は似た型を意識してみたいと思います。

お礼日時：2009/11/19 08:53

No.7 回答者： KURUMITO 回答日時： 2009/11/19 16:07

解答Ｎｏ4です。

Ｂ3が空白のときにＢ４を空白にするのでしたらIF(ISBLANK(B3),"",のような式にするのは間違いです。Ｂ3セルには見かけ上空白になっていても数式が入っていますのでBLANK(空白)ではありません。IF(B3="","",のように使うことが必要です。
確認のために他のセルに=ISBLANK(B3)と入力してみましょう。答えはFALSEとなります。Ｂ3セルに式がある以上、空白としては認識されません。

この回答への補足

なるほど・・・BLANKは完全に空白の時に使うべきものだったんですね。勉強になります。

それと同時に修正を加えてみたところ、無事解決できました！

皆様ご丁寧にありがとうございました。
とてもたすかりました！

補足日時：2009/11/19 16:21

No.6 回答者： DIooggooID 回答日時： 2009/11/19 13:13

0:00　　を表示する場合の条件

0:00　　を非表示にする場合の条件


これを教えて下さい。

この回答への補足

0:00を表示する場合・・・8:30までに出勤、17:00以降の退社

0:00を非表示にする場合・・・出勤しておらず、空欄になっている

となっています。
決められた時間までに来て、決められた時間以降に退社すれば0:00がつきます。

補足日時：2009/11/19 16:18

No.5 回答者： DIooggooID 回答日時： 2009/11/19 11:42

Ｂ４セルの表示形式を、　ユーザ定義　で　h:mm;h:mm;

にしてみてください。

表示形式は、　　　　正の場合;　負の場合;　零の場合

です。

この回答への補足

消えました！・・・しかし問題が；；

私の説明が足りませんでした。
遅刻・早退をしていない場合には0:00をつけています。

全部選んでユーザ定義を変えたところ、正しく0:00が表示されてる場所も消えてしまいました。
やはり１個１個選択してやるしかないのでしょうか？

補足日時：2009/11/19 12:41

No.4 回答者： KURUMITO 回答日時： 2009/11/18 17:33

簡単な式は次のようになりますね。

Ａ4セルへの入力式は
=IF(A3="","",IF(A3*1>"8:30"*1,A3-"8:30",0))
1をかけることでＡ3が文字列としての時刻をシリアル値に変換します。TIMEVALUE関数を使うのと同じ意味になります。
Ａ３ではText関数を使っていますのであくまでも文字列で表した、例えば8:15にすぎません。シリアル値に直して比較に使うことが必要です。

この回答への補足

すみません、質問は解決したのですが・・・また新たな問題が。
こちらに補足させていただきます。

タイトルのとおり退勤時間の計算もしてるのですが、B1には退勤時間(例1759)を入力してあり、同じようにB3、B4にも出勤と同じように数式が入れてあります。

参照したB1セルが空白だった場合に、B3のセルにはきちんと空白が表示されるのですが、B4に0:00が表示されてしまいます・・・。

B1が空白の場合は空白で表示したいのですが、セルの関数に関わらず値がなければ空白にする、といったようなことは出来るのでしょうか？

調べてみましたがISBLANKをうまく使えてないのか、わかりませんでした。
無知ですみません・・・おわかりになるかたはいらっしゃるでしょうか？

補足日時：2009/11/19 09:35

この回答へのお礼

１をかけるとシリアル値になるというのは初めて聞きました・・・奥が深い

シリアル値に直さないといけない、ということですね。とても勉強になりました！


遅くなりましたが皆様丁寧にありがとうございました。おかげで解決いたしました。

お礼日時：2009/11/19 08:56

No.2 回答者： zongai 回答日時： 2009/11/18 17:08

=IF(ISBLANK(A3),"",IF(TIMEVALUE(A3)>TIMEVALUE("8:30"),A3-"8:30",0))

TIMEVALUEを追加してみました。

この回答へのお礼

これでも出来ました！
いろいろなやり方があるんですね・・勉強になります。

お礼日時：2009/11/19 08:49

No.1 回答者： DIooggooID 回答日時： 2009/11/18 17:04

この流れでいくのであれば、・・・・

=IF(ISBLANK(A3),"",IF(A3>=TIMEVALUE("8:30"),IF(A3-"8:30"<0,0,A3-"8:30"),0))

この回答へのお礼

出来ました！
ありがとうございます。

関数の組み合わせは難しいですね・・・

お礼日時：2009/11/19 08:48