EXCELでの条件判定について

質問

EXCELで出退勤の記録を取る為に表を作っているのですが、
J「出勤時刻」K「退勤時刻」L「残業」としたとき
「残業」時間を求める為にK列に

=(K9-J9-TIME(1,0,0))-TIME(8,0,0)　<…L9の場合

という式を作ったのですが、
休日などで出勤・退勤時刻が入力されていないときに
LのCELLに「####」と表示されてしまいます。
これを表示させないため、KやJに時刻が入力されていない場合（それぞれのCELLには「:」のみ入力されている）、LのCELLに「.」を表示し、両方に時刻が入力されていた場合には上記計算式の解が表示されるように

=IF(OR((J9=":"),(K9=":")),".","=(K9-J9-TIME(1,0,0))-TIME(8,0,0)")

という式を入れたのですが、今度はJ、Kが入力されているときに残業算出の計算式がそのまま表示されるようになってしまいました。

時刻が入力されていた際には計算式の解がLのCELLの中に入力されるようにするには一体どのような判定式を記述すればよろしいのでしょうか？おわかりになられる方がいらっしゃいましたら教えて下さい。よろしくお願いします。

回答No.3

maruru01 · ベストアンサー

こんにちは。maruru01です。

No.1の方の回答はちょっと違います。

=IF(OR(J9=":",K9=":"),".",K9-J9-TIME(1,0,0)-TIME(8,0,0))

（余分なかっこは取りました。）
なお、このままだと、残業がマイナスの場合（遅刻早退がある場合）にやはり、
「#####」
と表示されてしまいます。
それを回避する方法としては、

=--TEXT(IF(OR(J9=":",K9=":"),".",K9-J9-TIME(1,0,0)-TIME(8,0,0)),"[h]:mm;!0!:!0!0")

という数式にします。
これなら、残業が0以下は、全て「0:00」という表示になります。

お礼

ご返答ありがとうございます。
maruru01様に教わった通りに式を書いてみたところ、望んでいたとおりの結果を表示させることができました。
また何かあった際にはお力添えをよろしくお願いします。

回答No.4

maruru01

間違えました。
最後の数式は、

=IF(OR(J9=":",K9=":"),".",--TEXT(K9-J9-TIME(1,0,0)-TIME(8,0,0),"[h]:mm;!0!:!0!0"))

です。
私の数式は、J9とK9には「:」を入力して下さい。

回答No.2

dejiji-

=IF(((K9-J9-TIME(1,0,0))-TIME(8,0,0))<0,"",((K9-J9-TIME(1,0,0))-TIME(8,0,0)))
これでいかがでしょう。
＃＃＃が出るのは時間計算値がマイナスになるためにでます。エクセル（ウインドウズ版のみ）では時間のマイナスは計算できません。
よって、計算値がマイナスなら””とし、プラスなら計算結果を表示。
TIME(8,0,0)は、8/24=0.333でも代用できます。
あと入力するセルに「：」など何も入力しないで下さい。またまた、エラーが出ます。

お礼

ご返答ありがとうございます。
時間計算値がマイナスになると###が表示されると言うことをはじめて知りました。
大変勉強になりました。
また何かあった際にはご助言よろしくお願いします。

回答No.1

TheFool_No0

行いたい計算式=(K9-J9-TIME(1,0,0))-TIME(8,0,0)に「””」をつけているからだと思います。
「””」をつけると計算式じゃなくて文字として認識されてしまったんじゃないかな？と。

=IF(OR((J9=":"),(K9=":")),".",=(K9-J9-TIME(1,0,0))-TIME(8,0,0))
でＯＫだと思います。
…オレはTIME関数とか分かりませんけど…。汗

お礼

ご返答ありがとうございます。
今回TheFool_No0様をはじめ、多くの方に教えていただき大変勉強になりました。
また何かあった際にはお力添えよろしくお願いします。