Excel2003 深夜残業時間の計算
少し長い質問になりますが、よろしくお願いいたします。
出社・退社の時間から、早朝残業や夕方残業、深夜残業など、
残業時間を算出する数式を作成していますがどうにもうまく出来ません。
残業となる時間の切り分けは、平日は以下のようになっています。、
早朝残業→5：00～8：30
基本時間→8：30～17：00
通常残業→17：00～22：00
深夜残業→22：00～翌朝5：00
例えば平日、朝6：00出社→23：00退社であれば、
早朝残業が2.5、通常残業が5.0、深夜残業が1.0となります。
深夜残業を出す数式を、以下のように作成してみたのですが、うまく算出できません。
C列に出社、D列に退社、入力はコロンを挟んだ時間の形式で、表示形式は[h]:mmです。
A列に日付、B列に祝日のフラグ、数式はG列に入れています。
=IF(C10="",0,IF(OR(TEXT(A10,"aaa")="日",TEXT(A10,"aaa")="土",B10="祝"),0,IF(AND(D10>=VALUE("22:00"),C10<=VALUE("5:00")),ROUND((MIN("29:00",D10)-"22:00")*24,2),IF(AND(C10>=VALUE("0:00"),C10<VALUE("5:00")),ROUND((MIN("5:00",D10)-C10)*24,2),0))))
ですが、出社13:00→退社27:00のところで、深夜残業が0.0になってしまい、
数式の IF(AND(D10>=VALUE("22:00"),C10<=VALUE("5:00"))をOR式に変更しました。
=IF(C10="",0,IF(OR(TEXT(A10,"aaa")="日",TEXT(A10,"aaa")="土",B10="祝"),0,IF(OR(D10>=VALUE("22:00"),C10<=VALUE("5:00")),ROUND((MIN("29:00",D10)-"22:00")*24,2),IF(AND(C10>=VALUE("0:00"),C10<VALUE("5:00")),ROUND((MIN("5:00",D10)-C10)*24,2),0))))
すると、出社0：00→退社18：30のところで、深夜残業が0.0になってしまいました。
両方をカバーできる数式はどのようになるのか、色々試しても出来ません。
お分かりになる方、いらっしゃいましたらお知恵を貸してください。
説明がうまく出来ていないかもしれません、分かりづらい文章で申し訳ありませんが
どうぞよろしくお願いいたします。
お礼
ユーザー定義が[$-F400]h:mm:ss AM/PM になっていて ご指導いただいた ユーザー定義にして [h]:mm に修正したら 問題点が解決できました。 本当に有難うございました。 喜びに堪えません。