[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『１５分毎の残業時間の計算など』（カジカ）
今まで手で計算をしていました、残業時間の計算をエクセルで管理したいと思ったのですが難しくて手間取っています。過去ログを検索してみたのですがわかりませんでした。出社時刻と退社時刻を入力するだけで残業時間と深夜残業が出るようにしたいです。どなたか教えていただけると助かります。
条件は８：３０～１7：３０が定時、１７：３０～１7：４５、２０：００～２０：１５まで休憩、２２：００～深夜残業になります。残業時間は１５分で区切りです。
出社時間 退社時間 残業時間 深夜残業 8:20 22:20 4:00 0:15
といった感じです。宜しくお願いします。
回答ではなくて申し訳ないのですが・・・・ 勤務時間の計算には色々と個別の条件があり、結構面倒になります。 Google などで「Excel 勤務時間 計算」等のキーワードで検索されたら いろんな説明のページがありますので、参考になると思います。 (Hatch)
条件が合わない場合があるかもしれませんが、とりあえず試してみてください。 A B C D E F G H 1 出社時間 退社時間 残業時間 深夜残業 始業時間 終業時間 休憩 深夜区分 2 8:30 17:30 20:00 22:00 3 8:20 22:20 4:00 0:15 C3セルに =IF(B3>F2,IF(B3>H2,H2-F2-"0:30",IF(B3>G2,FLOOR(B3-F2-"0:30","0:15"),IF(B3>F2,FLOOR(B3-F2-"0:15","0:15")))),"") D3セルに =IF(B3>H2,FLOOR((B3-H2),"0:15"),"") ちなみに、24:00を超える場合には24:20で入力 出社時間が8:30前の場合は全て8:30からとしてます。 （川野鮎太郎）
Hatchさん、アドバイス有難うございました。 川野鮎太郎さん、回答有難うございました。 早速試してみたところ、終了時間が17:31～17:45の間だと『＃NUM!』と表示される様です。 表示はこのままでも構わないのですが、 残業時間の合計を計算したいのですが何か良い方法はないでしょうか？何度もすいません！ｍ（＿＿）ｍ
失礼しました。下記に変えてみてください。 =IF(B3>=TIME(18,0,0),IF(B3>H2,H2-F2-"0:30",IF(B3>G2,FLOOR(B3-F2-"0:30","0:15"),IF(B3>F2,FLOOR(B3-F2-"0:15","0:15")))),"") （川野鮎太郎）
川野鮎太郎さんへ 出来ました！有難うございました！ 初心者なので式の意味が判らず、式をコピーするだけになってしまいましたが； 初心者でも判る程度で結構ですので説明して頂けないでしょうか？（カジカ）
(ﾉ∀｀)あいた～、私の一番苦手とするところの説明をお望みですか(^_^A; 説明の上手なKAMIYAさんが不在なので困りましたね。 まず、C3（残業時間）に入る以下の式ですが、IFを使った倫理式になってるわけです。 倫理式IFはエクセルの関数の説明でIF(倫理式,真の場合,偽の場合）になってます。 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~＼＿( ･_･)はい、ここテストにでますよ～。 以上の事から、 =IF(B3>=TIME(18,0,0), IF(B3>H2,H2-F2-"0:30", IF(B3>G2, FLOOR(B3-F2-"0:30","0:15"), IF(B3>F2, FLOOR(B3-F2-"0:15" ,"0:15")))), "") ①~~~~~~~~~~~~~~~~~ ②~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ③~~~~ 最初から言葉で表すと、 ①もしB3の値が18:00以上（TRUE）の時は、②を計算しなさい。そうじゃない場合には③を計算しなさい。となっています。
さらに、②の式IF(B3>H2, H2-F2-"0:30", IF(B3>G2, FLOOR(B3-F2-"0:30","0:15"), IF(B3>F2, FLOOR(B3-F2-"0:15" ,"0:15"))))で、 ④~~~~~~~ ⑤~~~~~~~~~~ ⑥~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ④もしB3の値がH2（深夜時間）より大きい場合には⑤残業時間H2-F2-"0:30"（22:00-17:30-0:30=4:00）を計算させなさい、 そうじゃない場合には⑥を計算しなさい。となっています。 ただし、H2-F2-"0:30"（22:00-17:30-0:30=4:00）22:00を超えた場合には必ず残業4:00なので H2-F2-"0:30"を省略して、TIME(4,0,0)でもいけるはずです。
さらに、⑥の式IF(B3>G2, FLOOR(B3-F2-"0:30","0:15"), IF(B3>F2, FLOOR(B3-F2-"0:15" ,"0:15"))))で、 ⑦~~~~~~~ ⑧~~~~~~~~~~~~~~~~~~~~~~~~~ ⑨~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ もし、B3の値がG2（休憩時間20:00)より大きい（先に22:00より大きい場合は計算させているので、20:00～22:00の条件）場合には、 ⑧残業時間FLOOR(B3-F2-"0:30","0:15")=22:00以下の数字-17:30-0:30（休憩2回分）の値を15分単位で切り捨てしなさい。 そうじゃない場合には⑨を計算しなさい。となっています。
またもやさらに(^_^A;、⑨の式IF(B3>F2, FLOOR(B3-F2-"0:15" ,"0:15"))で B3の値がF2より大きい（先に20:00より大きい場合は計算させているので、18:00～20:00の条件）場合には、 FLOOR(B3-F2-"0:15" ,"0:15")=20:00以下の数字-17:30-0:15（休憩1回分）の値を15分単位で切り捨てしなさい。となっています。
それで、最後に全ての条件が偽（18:00より前に終業）の場合には空白""としなさい。
ε-(´o｀) ふ～ 、こんな感じでしょうか。 長くなって説明もわかりづらいと思いますが、少しずつ区切って勉強されたら良いと思います。 （川野鮎太郎）
いや、倫理じゃなくて論理だから…（笑） （とおりすがる～）
(ﾉε｀*)ぷぷぷ、やらかしちゃいましたね～(^_^A; 私だったら、(*´ﾉｪ｀)こっそり～と直してﾞ(*･･)σ【ページの更新】ぽちっとな♪です（笑涙。 （川野鮎太郎）
川野さんにイジワルしてるわけじゃないですよぉ（汗 たぶん上の説明を読んで頭くらくらしてるだろうカジカさんがほっと一息つけるように 常連サマには犠牲になっていただいて、くすっと笑えるネタを…と。 だって川野さんてば、格好のタイミングでボケてくださるんですもん（笑） （とおりすがる～）
(T▽T)アハハ! いつものボケ役がここでも炸裂ってことですね～(^_^A; でも、ネタとしてならナイスなんですが・・・（号泣 さっきの、コメントをわざとですよ(*´ﾉｪ｀)コソーリに替えますか＾＾ （川野鮎太郎）
川野鮎太郎さん、説明有難うございました！流れだけでも何となくですが理解することができました。 もっとわかるように勉強したいと思います。 "倫理"の件は気付かず"ろんり"と読んでました…（--;） 勉強させてもらうだけでなく、楽しめました。有難うございました！ またお世話になるとおもいますが、その時はよろしくお願いします。（カジカ）
たびたび申し訳ありません… どうやら終了時間が20:45､21:00､22:00などの時に15分ぶん残業時間が多く足されて しまうようです。終了時間20:45 残業時間2:30のはずが2:45と表示されます。 少数誤差というものでしょうか？（カジカ）
(・_・o)ン？ 上記の場合は2:45でいいのでは？
20:45-17:30-0:15（休憩1回）=2:45 むしろ合わないのは20:00～20:15までのようです(^_^A; あれ？合ってませんね(ﾉ_<｡)なんかボケてきてるようです。 少し時間をください。 （川野鮎太郎）
上記は 20:45-17:30-0:15（休憩1回）=3:00ですよね。 2:45になるのは、誤差が出てるようです。 （川野鮎太郎）
ぎゃあ、やっぱり変だ(ﾉ_<｡) 20:00超えたら休憩2回ですよね（ToT) 20:45-17:30-0:30（休憩2回）=2:45 あってる？？(^_^A;
（川野鮎太郎）
何回もお見苦しいところ申し訳ございません。 出来れば上記、3つは消したいです(^_^A; 以下に訂正してみてください。 =IF(B3>=TIME(18,0,0),IF(B3>$H$2,$H$2-$F$2-"0:30",IF(B3>=$G$2+"0:15",FLOOR(TIMEVALUE(TEXT(B3-$F$2-"0:30","h:m")),"0:15"),IF(B3>$F$2,FLOOR(TIMEVALUE(TEXT(B3-$F$2-"0:15","h:m")),"0:15")))),"") （川野鮎太郎）
いえ、とんでもないです！！ 私こそ説明不足ですいません…厳密にいうと20:45という時間の場合は残業は2:30で 20:46～21:00までの時間だと残業は2:45です。前に教わった式だと20:45､21:00､22:00以外は うまく出ていたのですが、新しく教えていただいた式では上の条件では違ってしまうようです。（カジカ）
あれ？ということは、最初の条件が違うって事ですか？ >条件は８：３０～１7：３０が定時、１７：３０～１７：４５、２０：００～２０：１５まで休憩、２２：００～深夜残業になります。残業時間は１５分で区切りです。 この条件だけなら、最後に示した式で合うはずですけど。 20:45が残業2:30になる条件はなんでしょうか。 （川野鮎太郎）
最初の条件で合っています。15分で区切りですが、18:00、18:15…20:30､20:45といった 15の倍数の時間では15分間残業をやったとはされず切り捨てられてしまうのです。 18:01、18:16…20:31､20:46と、１分過ぎると15分間残業した。ということになるのです。 18:00という時刻だと残業は０、18:01で残業15分ということですので、20:45が残業2:30になります。 最後の式では18:00は残業15分と表示されてしまうのです。（カジカ）
納得しました。 これでそうだ！(^_^A; =IF(B3>=TIME(18,0,0),IF(B3>$H$2,$H$2-$F$2-"0:30",IF(B3>=$G$2+"0:15",FLOOR(TIMEVALUE(TEXT(B3-$F$2-"0:31","h:m")),"0:15"),IF(B3>$F$2,FLOOR(TIMEVALUE(TEXT(B3-$F$2-"0:16","h:m")),"0:15")))),"") （川野鮎太郎）
その条件でしたら、D列も違いますので =IF(B3>H2,FLOOR(TIMEVALUE(TEXT((B3-H2)-"0:1:0","h:m")),"0:15"),"") に、変えてください。 （川野鮎太郎）
ありがとうございます！早速試しましたが、20:01～20:14の間だけ＋15分されてしまいます。（；；） 一体どこがおかしいのかも判らなくて、聞いてばかりで本当にごめんなさい。 Ｄ列はどうやら前の式のままでも大丈夫そうです。（カジカ）
よ～し、今度こそ(ﾉ_<｡)ｸﾞｼｭ =IF(B3>=TIME(18,0,0),IF(B3>$H$2,$H$2-$F$2-"0:30",IF(B3>$G$2,FLOOR(TIMEVALUE(TEXT(B3-$F$2-"0:31","h:m")),"0:15"),IF(B3>$F$2,FLOOR(TIMEVALUE(TEXT(B3-$F$2-"0:16","h:m")),"0:15")))),"")
D列は変えないと、22:15までの場合に深夜残業15分つきますけど？ 深夜の場合は15分でも付くのかな？(^_^A; （川野鮎太郎）
かっ…完璧です！有難うございます！！！ Ｄ列はやはり変えないとまずかったようです。失礼しました。 こんなに長い時間、そして最後まで丁寧に教えていただき有難うございました。 最後の式は自分なりに考えて勉強させていただこうと思います<(__)>（カジカ）
ε-(´o｀) ホッ！良かったです～＾＾ ！！！ ←3本いただきました～＾＾ｖ （川野鮎太郎）
再びすいません。ちょっと勘違いしてまして… > 20:01～20:14の間だけ＋15分されてしまいます。 といった時に、+１５分されてませんでした。そして実際は、 20:15分の時のみ15分足りないです。 そして、最後に教えて頂いた式の方は20:01～20:15に－15分されてしまうようです。
=IF(B3>=TIME(18,0,0),IF(B3>$H$2,$H$2-$F$2-"0:30",IF(B3>$G$2+"0:15",FLOOR(TIMEVALUE(TEXT(B3-$F$2-"0:31","h:m")),"0:15"),IF(B3>$F$2,FLOOR(TIMEVALUE(TEXT(B3-$F$2-"0:16","h:m")),"0:15")))),"") 三つ目のIFの後の「>=」となっているのを>に変えただけですが、これで合ってますよね!? (カジカ)
(ﾉ∀｀)あいた～、また違いましたか？(^_^A; 上記式と、私が最後に提示した式が違うようですが・・・ 最後の式で駄目でしたか？ 20:01～20:15のあいだは、休憩時間なので残業は増えないと解釈したのですが。 （川野鮎太郎）
20:01～20:15のあいだは、残業は増えない。で合ってます！ 20:00=残業2:00、20:01～20:30=残業2:15ですが、 最後の式では20:01～20:15=2:00（実際よりマイナス15分）となってしまうようです。 すいません、私が15分プラスされてるとか言ったからですね； (カジカ)
(・_・o)ン？
>20:01～20:15のあいだは、残業は増えない。で合ってます！ >20:00=残業2:00、20:01～20:30=残業2:15ですが、 であれば、20:01～20:15までは休憩してるから残業増えずに2:00で良いですよね・・・ 20:16を過ぎて～20:30が2:15。 合ってますか？ 最後の式は、この条件でやってますが。 （川野鮎太郎）
19:46～20:00が残業2:00なので、20:01(20:00時を1分過ぎて15分換算なので)～=2:15です。 20:01～20:15は休憩なのでそのまま2:15で、さらに 20:16～20:30で帰っても残業は増えず2:15のままで20:31～=残業2:30で、 20:01～20:15で帰った場合に、19:46～20:00分の残業が足されない事になります。 (カジカ）
最後の >20:01～20:15で帰った場合に、19:46～20:00分の残業が足されない事になります。 この意味がわかりません・・・20:01～20:15で帰った場合には、2:15ですか？ ということは、20:00過ぎたら休憩するだけで、残業が15分増えるんですね(^_^A; （川野鮎太郎）
ということであれば、カジカさんの合ってますよね？であげられた数式 =IF(B3>=TIME(18,0,0),IF(B3>$H$2,$H$2-$F$2-"0:30",IF(B3>$G$2+"0:15",FLOOR(TIMEVALUE(TEXT(B3-$F$2-"0:31","h:m")),"0:15"),IF(B3>$F$2,FLOOR(TIMEVALUE(TEXT(B3-$F$2-"0:16","h:m")),"0:15")))),"") で、合うはずです。 （川野鮎太郎）
最後の式だと20:16になった時点で19:46～20:00分の残業が足されていますが、 本当は20:01になった時点でこの分の残業が足されて欲しかったんです。 そうなると、私の掲示した式で合うはずなんですよ(>_<)／ （カジカ）
(・_・o)ン？ >私の掲示した式で合うはずなんですよ(>_<)／ で、合わないんですか？
合うはずなんですよ＿/￣|○ il||li http://www.geocities.jp/ayutaro333/Excel/jikan1.html この表の内容で合ってますよね。 （川野鮎太郎）
↑の表で合ってます。 私が言葉足らずでお騒がせしてすいませんでした(-_-；） 最後まで有難うございました。
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.