エクセルで勤務時間を計算したい

質問

例えば、

セルAに出社時間）　9:00
セルBに退社時間）　16:00

と記入し、

2時間ごとに休憩15分あるので、
それを自動的にセルCに表示させたいです。

この場合ですと、
セルCに休憩時間）　0:45

そして、
拘束時間から、休憩時間を引いた勤務時間をセルDに表示させたいです。

セルDに勤務時間）　6:15

そして、
時給を仮に800円として、日給をセルEに表示させたいです。
*15分で200円の計算です

セルEに日給）　5000円

マクロは使わないでお願いします。

回答No.6

tom04 · ベストアンサー

No.４です！
No.１さんの補足を読ませてもらうと

前回の数式だと、偶数時間ぴったりの場合、15分余計に計算されますので、
C2セルを↓の数式に変更してください。
1分だけプラスしてみました。

=IF(COUNTBLANK(A2:B2),"",INT((B2-A2)/"2:01")*"0:15")
D・E列はそのままでOKです。

※　入力は「分」までとします。
※　尚、C・D列の表示形式はユーザー定義から
[h]:mm
としておいてください。m(_ _)m

お礼

こちらの関数でできあがりました。

これで計算が楽になりました。

ご回答いただいたみなさまもありがとうございます。

回答No.8

はる のパパ（@van62097）

お早うございます。
回答が遅れましたが、ANO.7さんと同じですが、休憩が与えられる時間の単位が違います。
休憩は2時間単位ですよね。
合計が奇数時間(残り3時間)の時のみ休憩時間も15分となるのですね。
どちらにしても時間単位だけ2とするか3とするか調整して下さい。

回答No.7

KURUMITO

No1さんへの補足の説明では3時間ごとに15分の休憩ですね。最初のご質問では2時間ごとに15分と説明されていますが3時間毎の誤りですね。
ところで入力の式ですがA1セルに出社時間、B1セルに退社時間としたときに、休憩時間をC1にするのでしたら次の式を入力します。

=ROUNDUP((B1-A1)/"3:00",0)*"0:15"

勤務時間のD1セルには次の式を入力します。

=B1-A1-C1

日給のE1セルには次の式を入力します。

=(D1/"1:00")*800

なお、B1,C1,D1のセルについてはセルの表示形式を時刻から指定してください。E1セルについては表示形式を標準にします。

ところで通常は例えば9:10の出社は9:15の出社とする、16:20の退社は16:15の退社とするなど切の良い時間で計算されることが多いですがそのようなことは必要が無いのですね。また、日給にしても百円単位で切り上げにするとかの処理が必要と思いますがどうなっているのでしょう。
参考までに
出社の時間を15分単位で切り上げにするのでしたら　CEILING(A1,"0:15")
退社の時間を15分単位で切り捨てにするのでしたら　FLOOR(B1,"0:15")
また日給を100円単位で切り上げるのでしたら　CEILING(E1,100)

回答No.5

esupuresso

添付画像を参考にしての説明ですが・・・

別表を作っておいてVLOOKUP関数で導く方法もあります

Officeのバージョンが2007以上の場合・・・
セルC2には　=IFERROR(VLOOKUP(B2-A2,$G$2:$H$6,2,1),"")
セルD2には　=(B2-A2)-C2
セルE2には　=D2/"0:15"*200

Officeのバージョンが2003以下の場合・・・
セルC2には　=IF(ISERROR(VLOOKUP(B2-A2,$F$2:$G$5,2,1)),"",VLOOKUP(B2-A2,$F$2:$G$5,2,1))
セルD2には　=(B2-A2)-C2
セルE2には　=D2/"0:15"*200

などね。

回答No.4

tom04

こんばんは！
一発で解決とはいかないかもしれませんが・・・

＞*15分で200円の計算です

とありますので、15分に満たない場合は切り捨てと考えています。

↓の画像でC2セルに
=IF(COUNTBLANK(A2:B2),"",INT((B2-A2)/"2:00")*"0:15")

D2セルに
=IF(COUNTBLANK(A2:B2),"",B2-A2-C2)

E2セルに
=IF(D2="","",INT(ROUND(D2/"0:15",5))*200)
という数式を入れ、C2～E2セルを範囲指定　→　E2セルのフィルハンドルで下へコピー！
で画像のような感じになります。

※　E列の数式にROUND関数を使用しているのは、浮動小数誤差をなくすために
小数点以下を５桁に丸めています。m(_ _)m

回答No.3

noname#198951

毎時2時間毎に必ず15分の休憩が入るなら作業時間から2を割った回数だけ15分の休憩回数が入る計算に。

時給計算ですが、これは最低時間と言うのはないのでしょうか？
業務によっては何分までの時間は切り捨てられるなどの決まりがあったりしますが、その部分があるのを最初から考慮して計算する必要はないのでしょうか？
また、"時給を仮に"と書いてありますが、その部分が後に変わる可能性があるのであれば、計算式に最初から入力しておくより、『時給○×円』を設定するセルを作成しておく必要がありますが。

最終的に日給から月給まで導き出すなどの必要があるなら、その部分まで考慮して作る必要もありますよ。

回答No.1

はる のパパ（@van62097）

例えばとありますが、あなたが要求する質問が漠然としていませんか。
最初から勤務時間が分かっているなら、式を入れてまで計算する必要は無いです。
具体的な説明をしないと回答はいい加減なものになりませんか。
作業時間数の合計が偶数か奇数か、奇数の場合の扱いはどうなるのかが分かりません。

補足

出社時間、退社時間は毎回変わります。
毎日付けたいので、計算が面倒なので、自動的に算出できればと思いました。

拘束時間が、偶数の場合、例えば６時間ですと、休憩は２回の３０分です。
奇数の場合、例えば５時間の場合は、２回の３０分です。７時間の場合は、３回の４５分です。