エクセルで時給計算 教えてください！

質問

居酒屋の店長をやってます。
アルバイトさんの労働時間と給料を手計算でやっているのですが、大変で大変で。。
是非エクセルお詳しい方、助けてください。

営業時間は１７時から翌日０２時まで。
基本給は1,000円です。
２２時以降は深夜手当２５%増し。８時間労働以上の残業手当が２５%増し。深夜残業になると５０%増しになります。

A1に勤務開始時間、B1に勤務終了時間、C1に休憩開始時間、D1に休憩終了時間を入れるとして、労働時間実績、内深夜時間、内残業時間、給料が出る計算式を教えていただきたいのです。

ほとんど初心者です。
どうかよろしくお願いいたします。

回答No.8

_Kyle

#6です。

＞給料が０になってしまう

ありゃ、H1の数式が1列ずれていましたね。済みません。
正しくは
　=ROUNDDOWN((E1+F1*25%+G1*25%)*24*1000,0)
でした。

なお、#6で書き忘れていましたが、#6の数式の場合、
H列の表示形式は　[通貨][会計][数値]等にしてください。
　（　#,##0　とか）

------------------------------------------------
＞休憩時間を１５分単位で切り上げ

時刻はともかく、時間を切り上げるとなると、
#2さんが懸念されたような問題が発生するのですが…。

例えば、
　勤務開始：17:00
　勤務終了：03:00
　休憩開始：21:20
　休憩終了：22:20
の場合、

正味の休憩時間はキッカリ１ｈ
正味の労働時間はキッカリ９ｈ
ですが、
非深夜帯の休憩時間は40min．
　深夜帯の休憩時間は20min.
です。

　>>　このとき、深夜労働時間は？？　<<

------------------------------------------------

以下は、
総休憩時間・深夜帯休憩時間をそれぞれ別個に切り上げる場合です。
（上記の例だと、労働時間９h，深夜労働4.5hとする場合）

１行目は見出行とし、作業列を使う形にしました。
また、小数誤差対策のため整数化してから丸めるようにしました。

E2：(換)勤務開始時刻
　=CEILING(ROUND((MOD(A2-"12:00",1)+"12:00")*1440,0),15)/1440

F2：(換)勤務終了時刻
　=FLOOR(ROUND((MOD(B2-"12:00",1)+"12:00")*1440,0),15)/1440

G2：(換)休憩開始時刻
　=MOD(C2-"12:00",1)+"12:00"

H2：(換)休憩終了時刻
　=MOD(D2-"12:00",1)+"12:00"

I2：勤務時間
　=F2-E2

J2：休憩時間
　=CEILING(ROUND((H2-G2)*1440,0),15)/1440

K2：労働時間
　=I2-J2

L2：(深)勤務時間
　=MAX(F2-MAX(E2,0+"22:00"),0)

M2：(深)休憩時間
　=CEILING(ROUND(MAX(H2-MAX(G2,0+"22:00"),0)*1440,0),15)/1440

N2：(深)労働時間
　=L2-M2

O2：残業時間
　=MAX(K2-"8:00",0)

P2：給料
　=ROUNDDOWN((ROUND(K2*1440,0)*4+ROUND(N2*1440,0)+ROUND(O2*1440,0))/4/60*1000,0)

として、
・E:O列の表示形式を　h:mm　にする。
・P列の表示形式を　#,##0　にする。
・不要な列を非表示にする。
--------------------------------------------------------------
以上ご参考まで。長乱文陳謝。

回答No.7

gyouda1114

回答番号：No.3.4.5です
回答をすべて取り消して下さい。
私の回答では、残業部分重複して計算されます。

回答No.6

_Kyle

様子見していたのですが、イマイチすっきりしない感じがするので…。

E1：労働時間実績
　=(MOD(B1-"12:00",1)-MOD(A1-"12:00",1))-(MOD(D1-"12:00",1)-MOD(C1-"12:00",1))
F1：内深夜時間
　=MAX(MOD(B1-"12:00",1)-MAX(MOD(A1-"12:00",1),0+"10:00"),0)-MAX(MOD(D1-"12:00",1)-MAX(MOD(C1-"12:00",1),0+"10:00"),0)
G1：内残業時間
　=MAX(E1-"8:00",0)
H1：給料
　=ROUNDDOWN((F1+G1*25%+H1*25%)*24*1000,0)

として、E:G列の表示形式を　[時刻]　に。
--------------------------------------------------------------
●補足（仕様）

　【参考として】既出回答との【相違点】について補足します。
　正誤や是非について言及する意図はありません。

　・給料の計算方法の解釈について

　　例えば、
　　　勤務開始　：　17:00
　　　勤務終了　：　04:00
　　　休憩開始　：　22:00
　　　休憩終了　：　23:00
　　の場合
　　#1さんの計算方法では12,250円，
　　#3さんの計算方法では18,500円になりますが、
　　私の回答では
　　　非深夜・非残業　：　5h　×　1,000円
　　　　深夜・非残業　 ：　3h　×　1,250円
　　　　深夜・　残業　 ：　2h　×　1,500円
　　と解釈して、11,750円となるよう式を立てています。

　　また、
　　　勤務開始　：　23:00
　　　勤務終了　：　02:00
　　　休憩開始　：　-
　　　休憩終了　：　-
　　の場合
　　#1さんの計算方法では4,000円，
　　#3さんの計算方法では8,000円になりますが、
　　私の回答では
　　　深夜・非残業　：　3h　×　1250円
　　と解釈して、3750円となるよう式を立てています。

　・入力値について

　　[午前0時30分]は[0:30]　[24:30]　どちらで入力しても構いません。
　　[午前2時30分]も[2:30]　[26:30]　どちらで入力しても構いません。

　　「[26:30]のように入力する」とあらかじめ決めてしまえば数式は多少短くできますが、
　　数式の設定は１度きりなのに対して、入力は毎回のことなので。

　・丸めについて

　　時刻の切り上げ・切り捨てについては、
　　質問文でも補足でも触れられていないので、
　　そのまま正味の時刻・時間で計算しています。

　　給料額については、最後に１円未満の端数を切り捨てています。

以上ご参考まで。長乱文陳謝。

お礼

質問者です。
ご丁寧にありがとうございます。
週末忙しくてお礼が遅くなり申し訳ありませんでした。

早速、教えていただいたとおり式を入れてみたのですが、給料が０になってしまうのですけどご指導いただけますでしょうか？

また再度お願いなのですが時刻の切り上げ、切り捨てについては１５分単位で行っております。

勤務開始　切り下げ　１７時１分打刻→１７時１５分で開始したとする

勤務終了　切り上げ　０４時１分打刻→０４時００分で終了したとする

休憩に関しては
休憩開始　２２時１分打刻→時刻通り
休憩終了　２３時１分打刻→時刻通り
で休憩時間を１５分単位で切り上げ

といった具合に計算しております。

大変図々しいのですが、この計算式も教えていただけませんか？

是非、よろしくお願いいたします。

回答No.5

gyouda1114

回答番号：No.3.4です

修正です
修正後
H3に　=IF(G3>=8/24,8/24,G3)　8時間以内
修正前
H3に　=IF(G3>8/24,"8:00",G3)　8時間以内

なお、B列からJ列の時間の表示形式は。[h]:mmです。

回答No.4

gyouda1114

回答番号：No.3です。
補足します。
H2に　時給1,000　
I2に　22時以降単価1,250
J2に　8時間以上の単価1,250
です。

回答No.3

gyouda1114

回答番号：No.2 さんと同じ疑問は残りつつも

特に22時以降の休憩時間の扱い（この例は、単純にに休憩時間を差し引いた）
F3に　=(C3-B3)-(E3-D3)
G3に　=CEILING(F3,"0:15")　15分単位で切上げ
H3に　=IF(G3>8/24,"8:00",G3)　8時間以内
I3に　=IF(C3>=22/24,C3-22/24,0)　22時以降
J3に　=IF(G3>8/24,G3-8/24,0)　8時間超
K3に　=ROUND(SUM($H$2*(H3/"1:00"),I$2*(I3/"1:00"),J$2*(J3/"1:00")),0)　給与

回答No.2

tom04

こんばんは！
今、一生懸命Excelで数式を考えていましたが・・・
ちょっと疑問が生じてしまいました。
曖昧な回答になっては失礼なので回答は控えさせていただきます。

疑問というのは
(1)1日単位で考えるのか？
(2)もし1日単位で考えるのであれば、8時間労働以上の残業手当が25％増と言うことですが、
8時間労働以上の残業手当はすべて50％増になると思います。
(17：00～02：00まで働いた場合は1時間の深夜残業になりますよね？）
(3)休憩時間の時間帯によっては給料が変わってきますよね？
（たとえば、22：00までに休憩時間が終わった場合と22：00以降に休憩時間が終わった場合、22：00をはさんで休憩時間がある場合など・・・）
(4)時間給ということですが、分単位まできっちり計算した方がいいのか？
（15分単位で切り捨てるとか・・・？）

どうも私の読解力不足のせいか、そこらへんがはっきりしないので・・・
以上のことをクリアするような数式を考えていましたが、
かなり長い数式になりそうなので、途中であきらめました。
どうもごめんなさい！m(__)m

補足

言葉足らずで申し訳ありません。

ご協力ありがとうございました。

回答No.1

tortue2009

まだ勉強中なので　参考になればと思い　やってみましたが
Ａ～Ｄ列の表示形式は　ユーザー定義　[h]：ｍ　
AM02:00は、「26：00」と入力
Ｆ列：労働時間実績　[h]:m
Ｇ列：内深夜時間　　[h]:m
Ｈ列：内残業時間　　[h]:m
Ｉ列：給料　　　　　数値または通貨
という条件で
Ｆ１に、【=B1-A1-(D1-C1)】
Ｇ１に、【=IF(B1>TIMEVALUE("22:00"),B1-"22:00"-(IF(C1>=TIMEVALUE("22:00"),D1-C1,IF(D1>=TIMEVALUE("22:00"),D1-"22:00",0))),0)】
Ｈ１に、【=IF(F1>TIMEVALUE("8:00"),F1-"8:00",0)】
Ｉ列に、【=1000*(HOUR(F1)+MINUTE(F1)/60)+1000*0.25*(HOUR(G1)+MINUTE(G1)/60)+1000*0.5*(HOUR(H1)+MINUTE(H1))】

お礼

早々のご回答ありがとうございます。
私には式を見てもさっぱりなので。。。
とにかく打ちこんで試してみます。
ありがとうございました。