エクセル時間計算で24時以降の計算結果がおかしいのですがなぜこうなるのか教えてください。

以下の構文で出社時間をC4に退社時間をD4に入力し、設定されて居る休憩時間差引した結果をアウトプットさせたいのですが、退社時間が23時59分までは正常に計算するのですが、24時以降の数値を入力ると単に=D4-C4と同じ結果が表示されます。各セルの表示設定は[h]:mmとして24時以降の入力に対応しています。
どなたかお解りになる方いらっしゃれば教えてください。
よろしくお願いいたします。

********以下記載している構文です。
=IF(D4<>"",D4-C4

-IF(OR(TEXT(C4,"hh:mm")>="10:15",TEXT(D4,"hh:mm")<="10:00"),"0:00",IF(TEXT(D4,"hh:mm")>="10:15","10:15",TEXT(D4,"hh:mm"))-IF(TEXT(C4,"hh:mm")<="10:00","10:00",TEXT(C4,"hh:mm")))

-IF(OR(TEXT(C4,"hh:mm")>="13:00",TEXT(D4,"hh:mm")<="12:00"),"0:00",IF(TEXT(D4,"hh:mm")>="13:00","13:00",TEXT(D4,"hh:mm"))-IF(TEXT(C4,"hh:mm")<="12:00","12:00",TEXT(C4,"hh:mm")))

-IF(OR(TEXT(C4,"hh:mm")>="15:15",TEXT(D4,"hh:mm")<="15:00"),"0:00",IF(TEXT(D4,"hh:mm")>="15:15","15:15",TEXT(D4,"hh:mm"))-IF(TEXT(C4,"hh:mm")<="15:00","15:00",TEXT(C4,"hh:mm")))

-IF(OR(TEXT(C4,"hh:mm")>="18:30",TEXT(D4,"hh:mm")<="18:00"),"0:00",IF(TEXT(D4,"hh:mm")>="18:30","18:30",TEXT(D4,"hh:mm"))-IF(TEXT(C4,"hh:mm")<="18:00","18:00",TEXT(C4,"hh:mm")))

-IF(OR(TEXT(C4,"hh:mm")>="22:00",TEXT(D4,"hh:mm")<="21:30"),"0:00",IF(TEXT(D4,"hh:mm")>="22:00","22:00",TEXT(D4,"hh:mm"))-IF(TEXT(C4,"hh:mm")<="21:30","21:30",TEXT(C4,"hh:mm")))

-IF(OR(TEXT(C4,"hh:mm")>="25:30",TEXT(D4,"hh:mm")<="25:00"),"0:00",IF(TEXT(D4,"hh:mm")>="25:30","25:30",TEXT(D4,"hh:mm"))-IF(TEXT(C4,"hh:mm")<="25:00","25:00",TEXT(C4,"hh:mm")))

-IF(OR(TEXT(C4,"hh:mm")>="29:00",TEXT(D4,"hh:mm")<="28:30"),"0:00",IF(TEXT(D4,"hh:mm")>="29:00","29:00",TEXT(D4,"hh:mm"))-IF(TEXT(C4,"hh:mm")<="28:30","28:30",TEXT(C4,"hh:mm")))

-IF(OR(TEXT(C4,"hh:mm")>="33:00",TEXT(D4,"hh:mm")<="32:00"),"0:00",IF(TEXT(D4,"hh:mm")>="33:00","33:00",TEXT(D4,"hh:mm"))-IF(TEXT(C4,"hh:mm")<="32:00","32:00",TEXT(C4,"hh:mm"))),"")

No.2 ベストアンサー 回答者： tatsu99 回答日時： 2016/12/27 22:18

No1の方の方法なら、

[h]:mmでなく、[hh]:mmとしてください。

本件の不具合は、文字列で時刻の大小を判断しているのが原因です。
"hh:mm"の場合は、C4=24:00を TEXT(C4,"hh:mm)で変換すると"00:00"になり、"24:00"の文字列が
作れません。従って、２４時間を超えると、不具合が発生します。
"[h]:mm"の場合は、C4=9:00を TEXT(C4,"[h]:mm")で変換すると、"9:00"になります。
これで、"24:00"と比較すると、"9:00"のほうが大きくなります。

TEXT(C4,"[hh]:mm")の場合は、両者の上記の欠点をきれいに解消できます。

しかしながら、本来は、時刻の比較は、時刻同士で比較すべきであり、文字列で比較すべきではありません。
C4と"24:00"を比較するなら、
IF(C4>=TIMEVALUE("24:00"),xx,yy)のようにすべきです。
あなたの式をTIMEVALUEで比較するように変えたものが、以下の式になります。

=IF(D4<>"",D4-C4

-IF(OR(C4>=TIMEVALUE("10:15"),D4<=TIMEVALUE("10:00")),"0:00",IF(D4>=TIMEVALUE("10:15"),"10:15",D4)-IF(C4<=TIMEVALUE("10:00"),"10:00",C4))

-IF(OR(C4>=TIMEVALUE("13:00"),D4<=TIMEVALUE("12:00")),"0:00",IF(D4>=TIMEVALUE("13:00"),"13:00",D4)-IF(C4<=TIMEVALUE("12:00"),"12:00",C4))

-IF(OR(C4>=TIMEVALUE("15:15"),D4<=TIMEVALUE("15:00")),"0:00",IF(D4>=TIMEVALUE("15:15"),"15:15",D4)-IF(C4<=TIMEVALUE("15:00"),"15:00",C4))

-IF(OR(C4>=TIMEVALUE("18:30"),D4<=TIMEVALUE("18:00")),"0:00",IF(D4>=TIMEVALUE("18:30"),"18:30",D4)-IF(C4<=TIMEVALUE("18:00"),"18:00",C4))

-IF(OR(C4>=TIMEVALUE("22:00"),D4<=TIMEVALUE("21:30")),"0:00",IF(D4>=TIMEVALUE("22:00"),"22:00",D4)-IF(C4<=TIMEVALUE("21:30"),"21:30",C4))

-IF(OR(C4>=TIMEVALUE("25:30"),D4<=TIMEVALUE("25:00")),"0:00",IF(D4>=TIMEVALUE("25:30"),"25:30",D4)-IF(C4<=TIMEVALUE("25:00"),"25:00",C4))

-IF(OR(C4>=TIMEVALUE("29:00"),D4<=TIMEVALUE("28:30")),"0:00",IF(D4>=TIMEVALUE("29:00"),"29:00",D4)-IF(C4<=TIMEVALUE("28:30"),"28:30",C4))

-IF(OR(C4>=TIMEVALUE("33:00"),D4<=TIMEVALUE("32:00")),"0:00",IF(D4>=TIMEVALUE("33:00"),"33:00",D4)-IF(C4<=TIMEVALUE("32:00"),"32:00",C4)),"")

添付の図にそれぞれの結果が表示されていますので、参考にしてください。

この回答へのお礼

お礼
tatsu99さま
ご教授いただきましてありがとうございました。
TIMEVALUEコマンドと使うと云うことは全く分かりませんでした。
頂きましたご回答で全て解決致しました。
本当に有難うございます。なんと感謝申し上げてよろしいか。。。
重ねてお礼申し上げます。
また今後共よろしくお願い申し上げます。

お礼日時：2016/12/27 23:57

No.1 回答者： 銀鱗 回答日時： 2016/12/27 19:40

"24:10"-"17:15"

が正しい結果を返さない…と言う事でしょうか。

・・・まあ良いでしょう。気になった点・・・
計算式の中で使っているTEXT関数で、書式に"hh:mm"が使われている。
ここで24時間で割った余りだけが数値として扱われることが原因と予想してみる。
"[h]:mm"にして結果を確認してみてください。


・・・余談・・・
"hh:mm"を"[h]:mm"へは置換で一発変換可能です。

この回答へのお礼

ご回答ありがとうございます。
「24時以降の数値を入力ると単に=D4-C4と同じ結果が表示されます」では、分かりませんよね、、すみません。
例えば
C4の出社時間に9：00
D4の退社時間に23：59
を入力したときの結果は12：29ですが、
C4の出社時間に9：00
D4の退社時間に24：00
を入力したときの結果では1分しか変わらないはずなのですが、15：00となってしまいます。
ちなみに、残念ながら"[h]:mm"にしてみたのですが、結果は変わりませんでした。
お手数をおかけしてすみません。よろしければご教授頂ければ幸いです。

お礼日時：2016/12/27 21:21