エクセルの時間計算の数式を教えてください

質問

勤務管理をエクセルで作成しています。

19時前と、19時後の時間給を分けて支給することになり、
分けて集計をしたいのですが設定の仕方がわかりません。。。

出社１２：００　退社２２：００　とすると

１２：００～１９：００　の　「７：００」
１９：００～２２：００　の　「３：００」
を自動計算したいのですが、どうしたらよいでしょうか？

ただ、出社時間と退社時間は、出勤していない日は０：００と表示するようになってしまってます。
そのため、
Aセル：出社時間　Bセル：退社時間　Cセル：１９：００　Dセル：B-A-E　Eセル：B－C
にしみましたが、出社していない日の時間計算がうまくできません。。。

手計算は膨大な作業になってしまうので、自動計算させる方法を教えていただけると大変ありがたいです。
宜しくお願いします。

表示方法

回答No.4

KURUMITO · ベストアンサー

あなたが現在セルに入力されている式は次のようになっていることでしょう。
C2セル　　19:00
D2セル　　=B2-A2-E2
E2セル　　=B2-C2
これらの式での問題点がいくつかありますね。
（1）A2セルおよびB2セルが空白の場合でもE2セルにはエラーの######がD2セルには19:00が表示されてしまいますね。
(2) A2セルやB2セルに時刻が入力されていてもB2セルの値が19:00よりも以前の時間であればE2セルには#####が表示されますし、D2セルには正しい時間が表示されませんね。例えばA2セルに12:00、B2セルを18:00とした場合にはD2セルは6:00となるべきところが7:00となってしまいます。労働時間が増すことになり大きな誤りとなりますね。。
E2セルが#####のように表示されるのはB2-C2で－の時間になってしまう場合です。つまりC2の19:00よりもB2の時間が前の時間になっている場合です。
上に述べたような問題を解決するためには少なくとも現在の式を次のように変えることでしょう。
D2セルには　
=IF(COUNT(A2:B2)<>2,"",B2-A2-E3)
COUNT関数はA2セルとB2セルに時間（数値）が入力されている場合には2となりますので計算されますがそれ以外の場合には空白となります。
E2セルには
=IF(COUNT(A2:B2)<>2,"",IF(B2>=C2,B2-C2,0))
または
C2セルは常に一定のj時刻であるので
=IF(COUNT(A2:B2)<>2,"",IF(B2>="19:00"*1,B2-"19:00",0))
B2セルの時刻がC2セルの時刻よりも後の時刻であれば計算し、それ以外は0と表示されます。
なお、上の式で"19:00"*1としているのは時刻の文字列を数値としてのシリアル値に変換するためです。
これらの式をD2,E2に入力して下方にドラッグコピーすることで良いでしょう。
計算の結果はシリアル値として表示されますのでD列やE列を選択したのちに右クリックして「セルの書式設定」の「表示形式」のタブの中から時刻を選択して望みの表示にします。

お礼

ありがとうございました。
教えて頂いたとおりに作ってみたところ、できました！
本当にありがとうございます。

回答No.3

esupuresso

＞出社１２：００　退社２２：００　とすると
あくまでも、12；00以降の計算処理の場合ですが…

添付画像より参考にしていただければ・・・
セルC2に　=IF(AND(A2=0,B2=0),0,B2-A2)
セルD2に　=IF(C2=0,0,B2-"19:0")　の式を入れております

※ 出社時間と退社時間のセルに、休みの場合は直接「0：00」と入力した場合の計算処理となっております
※ 休憩時間などは計算処理の式には入っておりません

残業時間が次の日にまたぐ場合の処理方法として・・・
セルC2に　=IF(AND(A2=0,B2=0),0,B2+IF(B2-A2<0,1,0)-A2)
セルD2に　=IF(C2=0,0,IF(B2<A2,"19:0"-C2,B2-"19:0"))　の式を入れる方法などがあります

回答No.2

keithin

夜中の０時は２４：００、夜中の１時は２５：００のように入力するお約束で。

１９時前：
=IF(SUM(A1:B1),MIN(B1,"19:0")-MIN("19:0",A1),0)
１９時後：
=IF(A1<B1,MAX(B1,"19:0")-MAX("19:0",A1),0)

回答No.1

nao-y

出勤していなかった日のDセル、Eセルの値＝0:00と仮定して、

D2セル：=IF(B2=0,0,B2-A2-E2)
E2セル：=IF(B2=0,0,B2-C2)

でいかがでしょう？