タイムカードから就業時間計算

質問

Excel2000でタイムカードの出勤・退出時刻を入力して、就業時間を出そうとしています。
定時は8:30～17:15、休憩は12:00～12:50、14:50～15:00、17:15～17:30です。
http://www.h3.dion.ne.jp/~sakatsu/WorkingTimeCalc.htm
↑このサイトを参考に、

A列に出勤時刻(手入力)、
B列に退出時刻(手入力)、
C列に24時補正、
=IF(OR(A2="",B2=""),"",IF(A2<B2,B2,B2+1))
D列に深夜1(0:00～8:30)、
=IF(OR(A2="",C2=""),"",IF(AND(A2<TIMEVALUE("8:30"),C2>=TIMEVALUE("0:00")),MIN(C2,TIMEVALUE("8:30"))-MAX(A2,TIMEVALUE("0:00")),0))
E列に午前(8:30～12:00)、
=IF(OR(A2="",C2=""),"",IF(AND(A2<TIMEVALUE("12:00"),C2>=TIMEVALUE("8:30")),MIN(C2,TIMEVALUE("12:00"))-MAX(A2,TIMEVALUE("8:30")),0))

…の要領で、F列に午後1(12:50～14:50)、G列に午後2(15:00～17:15)、H列に夜(17:30～24:00)の式をそれぞれ入力し、I列でD～H列を合計しています。=SUM(D2:H2)

このうち午前・午後1・午後2は正確に表示されるのですが、深夜・夜が0:00のままで表示されません。(退出を夜or深夜にしても0:00のままです)
午前・午後と同じような式を使っているのにどうしてなのでしょうか。どうすれば正確に出るのでしょうか。
セルの書式設定はユーザー定義で[h]:mmにしています。
今日午後から連休明けまではここを覗けないのでお礼ができませんが、よろしくお願いします。

回答No.1

mshr1962 · ベストアンサー

D列の深夜ですが式間違えてませんか？
計算するのは早出(8:30以前の出社）と深夜残業(24:00以降の残業)の計なので
IF文は
=IF(OR(A2="",C2=""),"",IF(OR(A2<TIMEVALUE("8:30"),C2>=1),TIMEVALUE("8:30")-MIN(A2,TIMEVALUE("8:30"))
+MAX(C2,1)-1,0))

お礼

ありがとうございます！D列ちゃんと出ました！
=IF(OR(A2="",C2=""),"",IF(AND(A2<TIMEVALUE("終了時刻"),C2>=TIMEVALUE("開始時刻")),MIN(C2,TIMEVALUE("終了時刻"))-MAX(A2,TIMEVALUE("開始時刻")),0))
だと思っていたのですが…

ちなみに夜(17:30～24:00)のH列の式は
=IF(OR(A2="",C2=""),"",IF(AND(A2<TIMEVALUE("24:00"),C2>=TIMEVALUE("17:30")),MIN(C2,TIMEVALUE("24:00"))-MAX(A2,TIMEVALUE("17:30")),0))
となっているのですが(文字数オーバーで質問には書けませんでした)、
これはどこが間違っているのでしょうか？

回答No.2

mshr1962

#1のmshr1962です。
＞文字数オーバーで質問には書けませんでした
うまく、いかなかった方の式だけ載せればよかったのでは？

本題ですがtimevalue("24:00")はtimevalue("0:00")と同じ値(0)を返しますので
数字の1に置き換えてください。
後は普通に計算できると思います。

お礼

ありがとうございます！こちらも無事出ました！
なんでこうなるのか全くわからないのが悔しいですが…
これから連休明けまでここを覗けないので、取り急ぎお礼まで。
後ほどポイント発行させていただきます。