エクセルで時間外計算の仕方

質問

始業から終業時間がまちまちで、下記のような表を作成したいのですが上手くいきません。
何かいい方法はありましたら教えてくださいm(__)m

作りたい表↓
超過時間は10時間以上、15分未満切捨てとし、残業代は1時間当り500円とした場合
始業時間 　終業時間　稼動時間　　超過時間　　 残業代
　7:00　　　　18:15　　　　11:12　　　　　1:00　　　　　 　500
　5:00　　　　13:13　　　 　8:13　　 　　　0:00　　　　　　　　 0
23:00 　　　　12:16 　　　13:16　 　　　　 3:15 　　　　　　 1625

作ってみて失敗した表↓
Ａ1に10:00　Ｂ１に500と入力し
Ａ２に始業時間　Ｂ２に終業時間　Ｃ２に稼動時間　Ｄ２に残業時間　Ｅ２に残業代　として

Ａ2　　　Ｂ２　　　　　　Ｃ３　　　　　　　　　　　　　　Ｄ４　　　　　　　　　　　　　　　　　Ｅ２

7:00　　　8:15　　=FLOOR(B2+1-A2,"1:0")　　=FLOOR(C3+1-A1,"0:15")　　=D4*B1

※稼動時間が10時間未満で残業がつかない場合の計算ができない
※残業時間に残業代を掛けた計算の計が違う

以上、ＩＦ関数を使えばいいのかとも思ったのですが、どう使えば有効なのかも判らずにおりました。
よろしくお願いします。

回答No.3

mikakochan · ベストアンサー

本当ですね。なぜか分かりませんが、計算結果の0:15は１５分よりも小さいみたいです。（計算結果の0:15から１５分を引き算すると、マイナスを示す####になりました）
と、いうことで、Ｃ３のセルを
=IF(A2>B2,B2+1-A2,B2-A2)+TIME(0,0,30)
とすると、とりあえず計算ができます。その場しのぎですが、計算結果が１５分３０秒になるようにしてみました。
コンピューター関連のカテゴリにご質問されると、もっときれいな関数にできると思いますが、、
とりあえず、残業代が５００円という設定が気になります。最低賃金を割ってますよね(^^;

お礼

ありがとうございます(*^_^*)
無事に作成することができました！！
残業代500円は清算結果が自分でわかりやすいよう、とりあえず入れてみた数字だったりします（笑）
これからもっとエクセルを勉強しようと思います。
本当にありがとうございました！

回答No.2

mikakochan

＃１です。すみません、間違えてました。

始業時間も切捨てる場合ですが、
Ｃ２：=IF(A2>B2,FLOOR(B2,"0:15")+1-CEILING(A2,"0:15"),FLOOR(B2,"0:15")-CEILING(A2,"0:15"))
ですね。

回答No.1

mikakochan

単純に実労働時間の１５分切り捨てなら、
Ｃ２：=IF(A2>B2,B2+1-A2,B2-A2)
Ｄ２：=IF(C2<=$A$1,0,FLOOR(C2-$A$1,"0:15"))
Ｅ２：=$B$1*D2/"1:0"
で、いかがでしょうか？
Ｅ列の表示形式を標準や会計にしてください。数式入力後は自動で時刻の設定になってしまいますので。

始業時間も就業時間も切り捨てという場合（最高で２８分の切捨てが発生する）なら、
Ｃ２：=IF(A2>B2,FLOOR(B2,"0:15")+1-FLOOR(A2,"0:15"),FLOOR(B2,"0:15")-FLOOR(A2,"0:15"))
Ｄ２：=IF(C2<=$A$1,0,C2-$A$1)
Ｅ２：=$B$1*D2/"1:0"
こんな感じでしょうか。

お礼

ありがとうございます。
早速やってみたのですが、稼動が10:15、時間外が0:15の時は切り捨てられ、残業代は\0となってしまいました。
15分の残業代を出すにはどのようにすればいいでしょうか？
何度もすみませんが、もしよかったら教えてください。