- ベストアンサー
Excel2003 深夜残業時間の計算
- Excel2003で深夜残業時間を計算する数式がうまく作成できないと困っています。
- 平日の早朝残業、基本時間、通常残業、深夜残業の時間帯ごとに残業時間を算出する必要があります。
- 現在使用している数式では特定の条件で深夜残業が算出されない場合があり、どのような数式を使用すればよいかわかりません。
- maikimaiki
- お礼率70% (31/44)
- その他MS Office製品
- 回答数7
- ありがとう数5
noname#204879
- noname#204879
- みんなの回答 （7）
- 専門家の回答
質問者が選んだベストアンサー
その他の回答 (6)
- MarcoRossiItaly
- ベストアンサー率40% (454/1128)
＞0：00～5：00の間に出勤した場合が、0になってしまいました。 それは質問文にない時間パターンですので、当然、正しく計算しません。質問者さんの責任の範疇ですので、ご自分で必要なセルを補ってください。 重複する時間帯の長さ、例えば 5:00?8:30 という時間帯と 6:00?23:00 という時間帯が重複している範囲は 6:00?8:30 ですが、その長さ 2:30 を求める数式は、max(,min-max) ということになっています。回答者の責任ではありませんので、ご自分で応用してください。
お礼
回答を寄せてくださり、ありがとうございます。 質問文の中に考えられるパターンを提示せず申し訳ありませんでした。 自分でも色々勉強しないといけませんね。 どうもありがとうございました！
[No.2補足]へのコメント、 》 条件付けが全く出来ていないって事ですね 仰るとおりですね。 他に、例えば、08:25 に出社したら 08:30 までの５分間分の「早朝残業」を付けるのか否かという問題もあります。
お礼
アドバイスをくださったおかげで、こんがらがっていた頭を整理しなくては、と気付きました。 皆様にお付き合い頂き、感謝しています。 ありがとうございました！
- MarcoRossiItaly
- ベストアンサー率40% (454/1128)
C4 6:00 D4 23:00 F2 5:00 F3 8:30 G2 =f3 G3 17:00 H3 22:00 I3 29:00 I4 =max(,min($D4,I$3)-max($C4,I$2)) J4 =sum(f4:i4) K4 =d4-c4=j4 を、添付図では入力しています。数式を全部入力したら、時間が表示されているセル範囲を選択した状態で、右クリックなどから「セルの書式設定＞表示形式タブ＞ユーザー定義＞種類ボックス」に「[h]:mm」を入力。翌朝 1:00 を 25:00 などと表示します。
お礼
皆様にお付き合い頂き、感謝しています。 ありがとうございました！
補足
昨日は勤め先から帰宅してしまった為、確認が遅くなり申し訳ありません。 回答を寄せて頂きありがとうございます！ 画像まで付けてくださり、とてもわかりやすかったです。 ただ、サンプルの時間を入れてみましたら 0：00～5：00の間に出勤した場合が、0になってしまいました。 （2：15→15：15、0：00→10：30など） ですが数式のヒントを頂けましたので自分でも、もっと試してみます！ どうもありがとうございました！
- KURUMITO
- ベストアンサー率42% (1835/4283)
曜日などの表示は別としてC２セルから下方に出社時間、D２セルから下方に退社時間が入力されているとしてE列に早朝残業を表示させるのでしたら次の式をE２セルに入力して下方にドラッグコピーします。 =IF(C2="","",IF(AND(C2<D2,C2<"8:30"*1),(MIN(D2,"8:30")-MAX(C2,"5:00"))*24,IF(AND(C2>D2,D2>"5:00"*1),(MIN(D2,"8:30")-"5:00")*24,""))) F列に通常残業を表示させるのでしたら次の式をF２セルに入力して下方にドラッグコピーします。 =IF(C2="","",IF(AND(C2<D2,D2>"17:00"*1),(MIN(D2,"22:00")-MAX(C2,"17:00"))*24,IF(AND(C2>D2,D2>"17:00"*1),(MIN(D2,"22:00")-"17:00")*24,""))) G列に深夜残業を表示させるのでしたらF２セルに次の式を入力して下方にドラッグコピーします。 =IF(C2="","",IF(AND(C2<D2,C2>="5:00"*1,D2>="22:00"*1),(MAX(D2,"22:00")-MAX(C2,"22:00"))*24,IF(AND(C2<D2,C2>=0,D2>="22:00"*1),(MIN(D2,"5:00")-C2+MAX(D2,"22:00")-MAX(C2,"22:00"))*24,IF(AND(C2<D2,C2>=0,D2<"22:00"*1),MIN(D2,"5:00")*24,IF(AND(C2>D2,D2<="22:00"*1),(MIN(D2,"5:00")+"24:00"-MAX(C2,"22:00"))*24,IF(AND(C2>D2,D2>"22:00"*1),(MIN(D2,"5:00")+MAX(D2,"22:00")-"22:00")*24,"")))))) なお、時間の入力については０：００から２３：５９までの入力とします。また２４時間以上の労働時間については考慮されていません。
補足
回答をありがとうございます！ 深夜残業を出すにはこんなに長い条件が必要なのですね…勉強になります、ありがとうございます。 ただ、サンプルの時間を入れさせて頂きましたら、出社7:30→退社18:30でも深夜残業が5時間と出てしまいました。 回答して下さった式は私には難しく、中身を理解するには時間がかかってしまいそうなので、どこで5時間となるのかは不明なのですが…。 ですが、面倒な式を考えて下さり感謝いたします！
当日の05:00より前に(例えば03:00)出社して、翌朝(例えば04:00)まで仕事をするなんてことは考えなくてよろしいのですか？ つまり、「出社は当日の05:00以降、かつ、退社は翌日の05:00より前」ということ？ 貴方の「説明がうまく出来ていないかもしれ」ないので、確認しています。
補足
早速ありがとうございます。 うまく説明できていなくてすみません。 確かに当日の05：00より前に出社して、翌朝4：00まで仕事をする、もあります・・・。 恥ずかしながら条件付けが全く出来ていないって事ですね・・・
- kuma310min
- ベストアンサー率40% (212/518)
考え方だけの説明になりますが、 ・作業用として、H列・I列を用意 ・退社が22：00以降ならば、その超過分を計算 → H列へ ・出社が5：00以前ならば、出社～5：00までを計算 → I列へ ↓ 上記2つを足す。（G列） ではダメなのでしょうか？ （どうしても作業用の列を使いたくない場合は、当然NGですけど）
お礼
作業列を使い算出する方法を教えてくださりありがとうございました！ こんがらがっていた頭を整理しなくては、と気付きました。 皆様にお付き合い頂き、感謝しています。 ありがとうございました！
補足
早速回答をありがとうございます。 作業列を使う方法ですね、確かにその方がきちんと整理されていいのかもしれません！ 退社と出社と分けて数式を作ってみたいと思います。 ありがとうございました！
関連するQ&A
- エクセル２０１０ 深夜残業時間の計算
出社・退社の時間から、残業、深夜残業など、 残業時間を算出する数式を作成していますがどうにもうまく出来ません。 残業となる時間の切り分けは、平日は以下のようになっています。、 基本時間→8：30～17：15 残業→5：00～8：30 と17：15～22：00 深夜残業→22：00～翌朝5：00 休憩時間→0:45(C1) 基本勤務時間→8:00(B1) 例えば平日、朝6：00出社→23：00退社であれば、 早朝残業が2:30、通常残業が4:45、計7:15です。深夜残業が1:00となります。 F欄に時間外、 G欄に深夜残業としています。 現状は時間外を出すのに =SUM(E5-D5)-$B$1-$C$1-G5 と単純にしてます。(G5は手入力) これを自動で入力できたらと考えています。 5:00前に出勤することはほとんどありませんが、22:00を超えることはあります。 ただし、24:00を超えると翌日計算になります。 過去欄を参考にしようにも理解できなく困っています。 http://okwave.jp/qa/q8147465.html よろしくお願いいたします。
- ベストアンサー
- Excel（エクセル）
- 残業代の計算方法（エクセル）
今残業代計算シートをエクセルで作っています。現在使用している計算式は =MIN(D6,"24:00")-MAX(C6,"18:00")（C6：出社時間 D6退社時間）でやっているのですが、こうすると休日の空欄のところに残業6:00時間と計算されてしまうのです。 出社時間と退社時間を入力して残業時間が一発で出てくるような計算式はないでしゅうか？ よろしくお願いいたします。
- ベストアンサー
- 掃除・洗濯・家事全般
- 残業時間の合計が
B F列 開始時間 C G列 退社時間 D H列 残業時間 E I列 残業手当 を入れて３１日まで入れています。 下記のような数式を(H９)に入れて３１日分まで(H３９) =IF(AND($F9<G$9,$G9>D$5),MIN($G9)-MAX($F9,D$5),0) ３１日分まで(H３９)までコピーしています 残業時間の合計時間ですが =SUM(D9:D39) この列はちゃんとイイです =SUM(H9:H39) この列は３０日迄はいいのですが３１日 に入力されると０．００となります ３０日迄はいいのです なにが問題なのか困っています どなたかご指導いただけませんか
- ベストアンサー
- オフィス系ソフト
- 【Excel】時間計算について
Excel2003を使用しています。 残業時間の計算について、下記のような場合、F2とG2には、どのような数式を入力すればいいでしょうか？ 22時以降は深夜残業となりますので、終了時刻が22時を過ぎた場合は、その部分の時間をG列の深夜残業に表示し、22時まではF列の残業に表示させたいのです。表示形式は、C列とE列は『時刻』、F列とG列は『h"時間"mm"分"』になっています。 C列 E列 F列 G列 1 開始時刻 終了時刻 残業 深夜残業 2 17時30分 23時30分 4時間30分 1時間30分 IFで条件分岐して入力してみたり、過去の質問も検索してみましたが、思うように表示させることができませんでしたので、質問させていただきました。 よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- 深夜勤務を含む時間外割増賃金計算シート
24時間稼動の工場で働く人の割増賃金を計算するためのタイムシートを作成中です。次のように進んでいます。尚、深夜残業時間帯は午後10時から翌朝5時までとしています。 Ｅ列：出社時刻 Ｆ列：退社時刻 Ｇ列：就労時間合計（休憩１Ｈのぞく） ＝IF(ISNUMBER(E8),F8-E8-TIME(1,0,0),"") Ｈ列：残業時間（8時間を超えた分、25%割増） ＝IF(OR(E8="",F8=""),"",MAX(MIN(F8+(F8<E8),22/24)-E8-18/48,0)) Ｉ列：深夜労働１ （休日以外、25％割増） ＝IF(OR(E9="",F9=""),"",MAX(F9(F9<E9),22/24)-22/24) Ｊ列：深夜労働２（休日以外で8時間を超えた部分、50%割増） ＊８Ｈを超える残業時間が深夜時間帯にかかる部分 Ｋ列：休日労働１ （35％割増） ＝IF(OR(E9="",F9=""),"",MIN(F9+(F9<E9),22/24)-E9-1/24) Ｌ列：休日労働２ （深夜労働時間帯にかかる部分、60％割増） Ｊ列とＬ列にどのように式を入れたらよいのかわからないのですが、現状だと他の列の式も変える必要がありそうです。というか全部変える必要あり？ 極端な例ですが、普通の日に10時出社、翌朝6時退社した場合、Ｈ列４Ｈ、 Ｉ列７Ｈとなるべきですが、現状だとＨ列３Ｈ，Ｉ列８Ｈと出てしまいます。 Ｊ列、Ｌ列の式を含めて、どのように修整したらよいのか、教えてください。。どうぞ宜しくお願い致します。何だかこれを作るのに疲れてしまって、いいフリーソフトが無いか探してみましたが、見つかりませんでした。。
- 締切済み
- オフィス系ソフト
- EXCELで関数を使って残業時間の計算がしたいのですが・・
EXCELを使って残業時間の計算したいと思っているのですが、関数にあまり詳しくないのでアドバイスお願い致します。 （A1）出社、（B1）退社時間は30分単位で自分で入力します。 D1に10時間以降残業にし、残業に満たない場合は未表示で30分単位は0.5と表示させようと思っています。 今現在 A1＝出勤時間 8:00 B1＝退社時間 20:00 C1＝労働時間 12:00 関数 =IF(A1="","",B1-A1) D1＝残業時間 =IF(C1="","",IF(C1>"10:00",(C1-"10:00")*24,"")) と、いう感じにやってみましたが、D1の計算がうまくいきません。よくわからないのですが、シリアル値というのが関係してるのでしょうか？宜しくお願い致します。
- ベストアンサー
- オフィス系ソフト
- エクセルで残業時間を時間帯によって＋１時間＋30分にしたい
関数がわからなくて困っています。 基本は8:30～17:30 残業（早出含む）とされる時間帯 5:00～7:30／17:30～19:30 この時間帯は1.0時間刻み （退社が18:00だと残業は0） 7:30～8:30／19:30～24:00 この時間帯は0.5時間刻み （退社が20:00だと残業は2.5） 休憩は1:00（12:00～13:00） 以上の条件でＣ列―出社時刻、Ｄ列―退社時刻、 Ｅ列―勤務時間、Ｆ列―残業時間 で表を作った場合、Ｆ列の残業時間を出す式がわかりません。 （Ｅ列の勤務時間もですね。自分で考えたのはアヤシイ） E/F列は、例えば1時間30分なら1.5と出てくるようにします。 7:30出社で20:00退社の場合、勤務時間は8.0、残業時間は3.5となる ようにしたいのです。 時間の計算が苦手で・・・。どなたか教えてください。
- ベストアンサー
- オフィス系ソフト
- [Excel]関数の入った空白セルについて
初めまして。 エクセル2003を使用してます。 似たような質問は多々あったのですがいまいちこちらが求める答えには導かれなかったので投稿させてもらいました。 関数についてはIFを時々使うくらいの知識しかありません。 会社で使う日報を作成しています。 A B C D 出社時間 退社時間 実働 残業 9:00 19:00 9:00 1:00 Cのセルには =IF(OR(ISBLANK(A1),ISBLANK(B1)),"",(((B1-A1)*24)-1)) と入力しました。 ちなみに9時出社で18時退勤、お昼1時間休憩があるのでマイナス1時間の合計8時間労働です。 休みの日は空欄なのでエラーにならないようにISBLANKを使い、ここまでは良かったのですが、 Dの残業を計算する際、残業があった場合のみ差し引いた時間が出るようにしたいので =IF(OR(ISBLANK(C1),C1<8.15),"",(C1-8)) このように入力したところ、出勤日でない日(Ｃ列の空欄セル)が＃VALUE!と出てしまいました。 数式が入っている空白セルがある箇所をエラーにならないようにするにはどのような関数を使えば良いのでしょうか？ 初歩的なことかもしれませんが、よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- エクセルでの深夜残業手当の計算方法
お知恵をお貸しください。 現在、A列=出社時間、B列=退社時間、Ｃ列=労働時間（A-B)、という表があります。 これから、以下の４つの答えを取り出したいです。 D列＝深夜残業：労働時間が８時間以上 かつ ２２時以降の労働時間 E列＝深夜：２２時以降の労働時間 から Ｄ列 をマイナス F列＝通常残業：労働時間が８時間以上 かつ ２２時未満の労働時間 G列＝通常：労働時間から上記D,E,Fを除いたもの 時間はすべて 13:00:00 の形でデータを保有しています。 どのように関数を書けばよいでしょうか。教えてください。
- ベストアンサー
- Excel（エクセル）
- エクセル日報 休日出勤 定時内も残業扱いにしたい
度々申し訳ありません。 完璧に出来たと思っていましたらまた問題が出てしまいました。 昨日http://okwave.jp/qa/q8167846.htmlで質問した者です。 早出 5：00～8：50 定時 8：50～17：20 普通残業 17：20～22：00 深夜 22：00～5：00 C D E F G H I ９ 出社 退社 昼休憩 夕方休憩 早出残業 普通残業 普通残業内休憩 J K L ９ 深夜残業 深夜残業内休憩 実労時間 G９セル =IF(ISBLANK(C9),"",IF(COUNT(C9:D9)<>2,"",MIN(D9,"8：50")-MIN(C9,"8：50"))*24) Ｈ９セル =IF(ISBLANK(C9),"",IF(O9="休日出勤",MIN(D9,"21:59")-C9,MAX("17:20",MIN(D9,"21:59"))-"17:20")*24) Ｊ９セル =IF(D9<=TIME(21,59,0),0,(D9-"21:59")*24) Ｌ９セル =(TIMEVALUE(TEXT((1+D9)-C9,"h:mm"))-TIME(0,E9,0)-TIME(0,F9,0)-TIME(0,I9,0)-TIME(0,K9,0))*24 という数式が入ってます。 Ｏ９セルが休日出勤入力欄です。 休憩時間は分単位(例えば45／分)で入力してもらうようにしました。 上記で作成したところ、通常出勤に於いては問題ないのですが、休日出勤として8：50より前に早出出勤した場合、早出残業欄にも早出分が表示されてしまい、普通残業分にも含まれる為重複してしまいます。 例えば8：20出社 17：05退社 昼休憩60分 と入力すると、早出残0.50 普通残8.75 実労7.75 と出てしまいました。 Ｏ９セルに休日出勤と入力した場合、早出残0.50を出ないようにし、普通残は昼休憩を差し引いた7.75にすることは出来るでしょうか？ 夕方休憩、普通残業内休憩は表の下部で残業合計から差し引くようにしてますので考慮しなくて大丈夫です。 ただ、休日出勤でない通常勤務の際は普通残業から昼休憩を引かれてはおかしくなってしまいますので、その辺りも検討頂けると有り難いです。 色々とややこしくて申し訳ありませんが、お力を貸して頂けると有り難いです。 説明不足ありましたらご指摘ください。
- ベストアンサー
- オフィス系ソフト
- 女性29歳の彼氏との付き合いが2年経った頃、彼の態度が急変しました。以前は結婚を意識しており、二人で楽しく過ごしていたのですが、ある日突然別れ話をされました。彼の理由は、誕生日のお祝いをするのが面倒くさくなり、好きではなくなったというものでした。女性は驚きと困惑に包まれ、彼と会って話をしたいと思っていますが、どうすればいいのか悩んでいます。
- 付き合いたての頃、彼の親に挨拶した際に反対されたものの、彼は女性と一緒にいたいと言ってくれたため、二人は付き合い続けてきました。しかし、二年経った頃に彼から突然結婚についての話が持ち上がりました。彼は婚約指輪を買うかどうか、どのようにプロポーズされたいかなどを女性に尋ねました。その後、彼が実家に帰ることになり、帰ってきた後に彼から別れ話をされました。女性はその理由に納得がいかず、彼との関係が急に変わってしまったことに戸惑っています。
- 彼からの別れ話を受け、女性は彼と会って話をする機会を持ちたいと思っていますが、彼は会わない方がいいと言っています。女性は彼の急な態度の変化について考え込んでおり、彼が実家に帰って親と話をして反対されたのではないかと疑っています。一度話をすることで問題が解決するかもしれないと思っている女性は、どのように行動すればいいのか迷っています。
お礼
皆様にお付き合い頂き、感謝しています。 本当にどうもありがとうございました！
補足
昨日は勤め先から帰宅してしまった為、確認が遅くなり申し訳ありません。 数式の修正まで寄せてくださりありがとうございます！ 早速、勤務時間を入力したところ、全ての時間で正しい深夜残業が算出出来ました！すごいですね・・・！ 自分では頭がこんがらがってしまい、お手上げだったので本当に助かりました。 ありがとうございました！