エクセルで時間計算（勤怠）の仕方を教えて！！

質問

エクセルを使って、勤怠時間表を作りたいんです。

＜決まりごと＞
始業時間…後の時間で30分単位（例　8：47→9：00、9：12→9：30）
終業時間…前の時間で30分単位（例18：20→18：00、19：59→19：30）休憩時間…1時間　　　　残業時間…18：00以降、30分単位
深夜残業…22：00以降、30分単位
※9：15に始業18：15に退社だと、実働時間は7.5時間で残業は0です。

例えば
Ａ始業時間　Ｂ終業時間　Ｃ実働時間　Ｄ通常残業　Ｅ深夜残業
　　9：17　　　22：40　　　12.0Ｈ　　　4.0Ｈ　　　　0.5Ｈ
ＡとＢを入力すれば、Ｃ～Ｅがでてくる、というエクセルの計算式を教えて下さい。

回答No.2

mu2011

「何もしない」とは、空白セルということであれば、「０」を「""」に置き換えてください。
Ｄは、=IF(FLOOR(B2,"0:30")>"18:00"*1,IF(FLOOR(B2,"0:30")>"22:00"*1,(FLOOR(B2,"0:30")-"18:00")*24-(FLOOR(B2,"0:30")-"22:00")*24,(FLOOR(B2,"0:30")-"18:00")*24),""),0)
Ｅは、=IF(FLOOR(B2,"0:30")>"22:00"*1,(FLOOR(B2,"0:30")-"22:00")*24,"")

お礼

何度もすみませんでした。ありがとうございました。

回答No.1

mu2011

一例です。
但し、始・終時刻入力の有無判定や日跨りについは考慮していません。
Ｃは、=(FLOOR(B2,"0:30")-CEILING(A2,"0:30")-"1:00")*24
Ｄは、=IF(FLOOR(B2,"0:30")>"18:00"*1,(FLOOR(B2,"0:30")-"18:00")*24-E2,0)
Ｅは、=IF(FLOOR(B2,"0:30")>"22:00"*1,(FLOOR(B2,"0:30")-"22:00")*24,0)
因みに、Ｃ～Ｅセルの書式設定は、ユーザ定義で0.0"H"として下さい。

お礼

細かく教えて頂き、ありがとうございました。職場で使いたいと思っていたので、本当に助かりました。

補足

本当にありがとうございます。早速やってみました。バッチリです。ただ、あと1点教えてもらっていいですか？　　Ｃ～Ｅのセルで、数値＝０の時は、何も表示しない、という設定の仕方を教えて下さい。お願いします。