- ベストアンサー
エクセルを使った就業時間の計算について
エクセルを使った就業時間の計算で質問です。 始業時間と終業時間の差し引きで拘束時間を計算して、 そこから休息時間を差し引き実働時間を計算する所までは 出来たのですが、実働時間から残業と深夜と深夜残業を自動で求めることが出来ません。 割り振りの条件は・・・ [深夜]当日0時から当日5時迄 [残業]当日5時から当日22時の間で、当日の実働が8時間を越えた部分。 [深夜残業]当日22時から29時の間で、当日の実働が8時間を越えた部分。 上記の条件で、自動計算をする為の式を教えて頂けませんでしょうか？ ※画像の様な形で自動計算できれば理想です。 よろしくお願いします。
- twincle-star
- お礼率46% (6/13)
- その他MS Office製品
- 回答数7
- ありがとう数1
- みんなの回答 （7）
- 専門家の回答
質問者が選んだベストアンサー
回答No6です。 深夜時間は次の式になりますね。 =IF(COUNT(D5:F5)<>2,"",IF(D5<"5:00"*1,MIN(F5,"5:00")-MAX(D5,"0:00"),0)) 他の時間についての式は前に示した通りです。
その他の回答 (6)
- KURUMITO
- ベストアンサー率42% (1835/4283)
最初のご質問では4時に始業で25時で終わって深夜時間は1時間となっていましたね。0時から5時までが深夜ならば2時間が正しいのではないでしょうか？ 私は1時間の答えとなるように始業の時間を考慮せずに最初の式を立てました。補足では２時の始業にしたら正解は3時間ですとなっていました。4時間が正解ではないのですか？
補足
回答ありがとうございます。 ＞0時から5時までが深夜ならば2時間が正しいのではないでしょうか？ 最初の質問では、4時始業で25時終業です。振り分けの条件で記載しておりますが、 １．当日の0時から5時が深夜です。 ２．当日の５時から22時までの間で、当日の実働が8時間（9時間の拘束）を越えた部分が残業です。 ３．当日の22時から29時までの間で、当日の実働が8時間（9時間の拘束）を超えた部分が深夜残業です。 ですので、 4時始業の場合は、5時までの１時間が深夜です。 5時以降22時迄で、始業から9時間の拘束時間を越えた部分。8時間が残業です。 そして、そこから25時迄の3時間が深夜残業です。 ですので、始業が2時の場合は、5時迄の3時間が深夜となります。 深夜残業は、当時の残業が深夜に掛かる場合が条件ですので、深夜とは違う意味になります。 ややこしいのですが、お力頂けたら助かります。 よろしくお願い致します。
- KURUMITO
- ベストアンサー率42% (1835/4283)
回答No4です。深夜時間について補足の中で次のような文章がありますね。 始業を2:00に変えてみたのですが、深夜が1：00と表示されました。 3：00と計算されると正しいのですが・・・。 2時から25時までの勤務で0時からの深夜時間は1時間と計算する式を使っていました。そうではなく答えは3時間ですとのことですが始業開始が深夜に始まるときの時間を考慮しておりませんでした。その時間を考慮するとなれば次のような式になります。 =IF(COUNT(D5:F5)<>2,"",MIN(F5,"29:00")-MAX(D5,"24:00")+MIN(F5,"5:00")-MAX(D5,"0:00"))
- KURUMITO
- ベストアンサー率42% (1835/4283)
H5セルには次の式を入力して下方にオートフィルドラッグします。 =IF(COUNT(D5:F5)<>2,"",F5-D5) J5セルには次の式を入力して下方にオートフィルドラッグします。 =IF(COUNT(D5:F5)<>2,"",H5-"1:00") L5セルには次の式を入力して下方にオートフィルドラッグします。 =IF(COUNT(D5:F5)<>2,"",IF(MIN(F5,"22:00")-MAX(D5,"5:00")-"1:00">"8:00"*1,MIN(F5,"22:00")-MAX(D5,"5:00")-"1:00"-"8:00","")) N5セルには次の式を入力して下方にオートフィルドラッグします。 =IF(COUNT(D5:F5)<>2,"",MIN(F5,"29:00")-MAX(D5,"24:00")) P5セルには次の式を入力して下方にオートフィルドラッグします。 =IF(COUNT(D5:F5)<>2,"",IF(J5>"8:00"*1,MIN(F5,"29:00")-MAX(D5,"22:00"),0)) いずれのセルも表示形式で時刻にします。
補足
回答ありがとうございます！ 早速試したみたのですが、始業時間と終業時間を変えたところ、深夜の計算が正しく計算されなく なってしましました。 始業を2:00に変えてみたのですが、深夜が1：00と表示されました。 3：00と計算されると正しいのですが・・・。 もし、お分かりでしたら教えて頂けますでしょうか？ よろしくお願いします。
- nattocurry
- ベストアンサー率31% (587/1853)
言い忘れましたが、計算するに当たって、「実働が8時間を超えた」を「拘束が9時間を超えた」に置き換えています。
- nattocurry
- ベストアンサー率31% (587/1853)
L5に =MAX(0,MIN(VALUE("22:00"),F5)-MAX(VALUE("5:00"),D5)-VALUE("9:00")) N5に =MAX(0,VALUE("5:00")-D5) P5に =MAX(0,MIN(VALUE("29:00"),F5)-MAX(VALUE("22:00"),D5+VALUE("9:00"))) でどうでしょう？
補足
回答ありがとうございます。 他のセルでも時間を変えて試してみました。キチンと計算されて出てきました！ 助かりました。 それと、合わせてお聞きしたいのですが、始業と終業を入力しない場合でも 残業8:00 深夜5:00 深夜残業7:00 が表示されてしまいます。 時間を未入力の場合は、残業等の時間が表示されないようには出来るのでしょうか？ ご存知でしたら、教えて下さい。 宜しくお願いします。
- nattocurry
- ベストアンサー率31% (587/1853)
添付画像の、残業8時間、深夜1時間、深夜残業3時間、というのは、どのように計算したのでしょうか？
補足
表示されている時間は手入力しただけです。計算はしていません。
関連するQ&A
- 【エクセル】休息時間の計算
休息時間の計算をしていて以下のようなものを計算したいのですが、 その最終目標の計算式に到達することができません。 参考に画像を添付いたします。ご教授ください。 -------------------------------------------- 表示したいのは「休息時間」の部分です。 １）前日の終業時刻、当日の始業時刻の両方が空欄の場合 →「休」と表示 ２）前日の終業時刻が空欄で当日の始業時刻に値が入力されてる場合 →24時間＋（当日0：00から当日始業時刻までの時間） の時間 ３）当日の始業時刻が空欄で前日の終業時刻に値が入力されてる場合 →24時間＋（前日終業時刻から当日0：00までの時間） の時間 ４）前日の終業時刻、当日の始業時刻の両方とも値が入力されている場合 →（当日の始業時刻）－（前日の終業時刻）の時間 ２）と３）をどうしたら良いかわからないので、 現在暫定的に２）か３）に該当する場合は「24時間以上」と表示するようにしており、 例えばセルE3には以下のような計算式を入れております。 =IF(AND($D2="",$C3=""),"休",IF(OR($D2="",$C3=""),"24時間以上",$C3-$D2)) 以上 宜しく御願い申し上げます。
- ベストアンサー
- オフィス系ソフト
- エクセルで時間計算（勤怠）の仕方を教えて！！
エクセルを使って、勤怠時間表を作りたいんです。 ＜決まりごと＞ 始業時間…後の時間で30分単位（例 8：47→9：00、9：12→9：30） 終業時間…前の時間で30分単位（例18：20→18：00、19：59→19：30）休憩時間…1時間 残業時間…18：00以降、30分単位 深夜残業…22：00以降、30分単位 ※9：15に始業18：15に退社だと、実働時間は7.5時間で残業は0です。 例えば Ａ始業時間 Ｂ終業時間 Ｃ実働時間 Ｄ通常残業 Ｅ深夜残業 9：17 22：40 12.0Ｈ 4.0Ｈ 0.5Ｈ ＡとＢを入力すれば、Ｃ～Ｅがでてくる、というエクセルの計算式を教えて下さい。
- 締切済み
- その他（ビジネス・キャリア）
- エクセルで時間計算を教えてください！！
作業の効率化と計算ミスを防ぐため、エクセルで勤務表を作成したいと思っております。 イメージとして A B C D E F G H 1列 日 出勤 退勤 残業 深夜 総労働 遅・早 休息 2列 1 4:20 17:42 4:22 0：40 12:22 3列 2 7:51 24:00 2:00 7:57 0:03 7:12 4列 3 0:00 10:50 1:00 5:00 9:00 0:50 5列 4 16:30 6:30 5:00 7:00 13:00 6列 5 8：30 17：30 8：00 A(日)・B(出勤）・C（退勤）・H列（休息）は、手入力しますが、 D（残業）・E（深夜）・F（総労働）・G（遅刻・早退）は、自動計算させたいと考えております。 F（総労働）は 退勤-出勤-休憩＝X (Xが8時間以上なら-1時間 8時間に満たない時はそのまま表示） D（残業）は、X-8時間 （Xが8時間に満たない時は、空白のまま） E（深夜）は、22：00から5：00とする。 G（遅刻・早退）は、Xが8時間に満たない時のみ （8時間-X＝Y）を表示させる。 ★出勤時間・退勤時間は、人や仕事の内容によりバラバラです。 H（休息）は、2日運行等になった時のみです。 休日など空白になっている時は、空白のまま。 説明が下手で申し訳ありませんが、お知恵を貸してください！！ 宜しくお願いします。
- ベストアンサー
- その他MS Office製品
- エクセルで勤怠時間(一部分の)計算方法
エクセルで社員の勤怠時間の計算を行いたいのですが 始業から終業までの、全ての時間ではなく 始業から終業までの間で行っている、一部の時間（開始時間～終了時間）だけを取り出し計算し さらにそれを、日中時間・残業時間・深夜残業時間に振り分けを行いたいです。 始業時間は、バラバラで午前の人もいれば午後の人もいます。 また、始業＝開始でない場合もあります。 色々と類似質問を参照し、式を入れているのですが振り分けがうまくいきません 以下に振り分けの際の条件と例を記載します。 C1：始業時間（手入力） D1：終業時間（C1+TIME(9,0,0)） E1：開始時間（手入力） F1：終了時間（手入力） G１,H１,I１：各時間帯から控除する休憩時間（手入力） J１：日中時間（始業時間～終業時間内の、8時間までの開始時間～終了時間） ※ここの式 K1：残業時間（22時までの終業時間を超過した、開始時間～終了時間） ※ここの式 L１：深夜残業時間（22時以降の終業時間を超過した、開始時間～終了時間） ※ここの式 例） (1)始業/9：00 終業/18：00 開始/13：00 終了/23：00 休憩１/0：30 休憩2/0：15 休憩3/0：00 結果→日中/4：30 残業/3：45 深夜残業/1：00 (2)始業/13：00 終業/22：00 開始/13：00 終了/24：00 休憩１/1：00 休憩2/0：00 休憩3/0：15 結果→日中/8：00 残業/0：00 深夜残業/1：45 お知恵をお貸し下さい
- 締切済み
- オフィス系ソフト
- エクセル関数で深夜勤務の計算
エクセルで社内の勤務表を作成しています。深夜勤務の時間計算の関数がわかりません。教えていただけないでしょうか？ 条件 1.深夜時間は22:00～05:00 2.始業、終業時間が深夜時間のみになる事もある 3.休憩時間も含めて計算したい。 例 1| A ｜ B ｜ C ｜ D ｜ E ｜ F ｜ G ｜ 2| 始業｜ 終業 ｜ 再入｜ 再終｜休憩｜ 実働｜深夜 3| 9:00 ｜23:00 ｜ 2:00 ｜ 6:00 ｜2:00 ｜16:00｜ 宜しくお願い致します。
- ベストアンサー
- その他(業務ソフトウェア)
- エクセル 就業時間計算について
エクセルで勤務時間計算をしたいと思っています。 ですが、どうしてもうまく計算ができません・・・ どなたかご存知の方、お力をかしてください<m(__)m> 例） A B C D E 出社時間 退社時間 通常残業 深夜 合計(勤務合計） １ 22：00 10：00 ２ 4：00 14：00 ３ 23：00 15：:00 勤務時間が不規則なので、出勤時間まちまちです・・・ それと、通常残業と、深夜勤務の時間をふりわけが出来れば尚助かります。 どうか C D E に入れる関数を教えてください
- ベストアンサー
- その他([技術者向] コンピューター)
- エクセルで就業時間の計算をしたいのですが・・・
初めてエクセルを使い苦戦しています。 現在締め日にタイムカードを税理士さんにコピーして 職員さんの給与計算をしてもらっています。 間違いがあってはいけないので 私も残業時間だけは計算して税理士からあがってきた時間と あっているか確認します。 が・・・パートさんの実働時間や正職員さんの残業時間が よく合わないのです。 私も税理士もタイムカードから入りと退出の時間を 差し引いて電卓で計算しています。 前回など３時間ほど計算が食い違い・・・ 友人がエクセルで簡単に計算できるのではないかというのですが なかなかうまくいきません。 クリニックなので午前と午後の２回、１日に入りと退出があります。 パートさんの場合１か月分の労働時間を分単位で計算して お支払いしています。 ８：３０から１２：３０、３：３０から７：３０が就業時間 で始まりは早めに入ってもカウントはしません。 遅く入った時はその時間からの計算になります。 一人体調の悪い方が見えて ８：５２～１２：４７ ３：２８～５：５８ なんて日が何日もありひとつずつ計算していくのが 大変なんです。 こういうのもエクセルで時間を入力すれば 簡単に計算できるのでしょうか？ それとも複雑な表計算になりますか？ まったく素人でわかりません。 よろしくお願いいたします。
- ベストアンサー
- Windows XP
- Excelで24時間対応の給与計算方法を教えてください
Excelを使って給与計算をしたいと思っているのですが、うまくいきません。 人数も多く勤務パターンも多いので計算式を作れないでいます。 日付をまたぐ勤務パターンもあるので 24時間で対応できる計算式ができればありがたいです。 ぜひ皆さんのお力をお貸し頂きたくお願いいたします。 セルには A：日付 B：曜日 C：出勤時間（夜勤例18.0で表示） D：退勤時間（夜勤例30.5で表示） E：実働時間（D-Cで求めてます） F：所定労働時間（22時～5時以外で9時間までの実働時間） G：時間外労働時間（22時～5時以外で実働9時間を超えた時間） H：深夜労働時間（22時～5時の実働時間） I：深夜残業時間（22時～5時で実働9時間を超えた時間） ※出勤時間・退勤時間は数値に変換し、退勤時間で24時を越えた時間に関しては＋24して基本の勤怠データを作っています。 関数計算式を利用して算出したいのは F：所定労働時間 G：時間外労働時間 H：深夜労働時間 I：深夜時間外労働時間 です。 例えば 日勤：出勤9.0 退勤24.0 実働15時間の場合は F:所定：9h G:残業：4h H:深夜：0h I:深残：2h 夜勤：出勤18.0 退勤31.5（朝の7時半）実働13.5時間の場合は F:所定：4h G:残業：2.5h H:深夜：5h I:深残：2h と計算結果を出したいのです。 給与の支払いに直接関わるのでとても困っています。 よろしくお願いいたします。
- 締切済み
- オフィス系ソフト
- エクセルでタイムカード計算
初心者です。エクセルで次のような計算をしたいのですが 基本の 始業時間19：00 終業時間1：10 で 実働 5：30 です 21：00～21：20 まで20分休憩を引きます 23：10～23：30 まで20分休憩を引きます 19：00より前は早出になります 1：20から残業ですが2Hに10分ずつ休憩が入ります 残業は30分単位です それで第2の設定として A 19：00～21：59 普通時間（早出含む） B 22：00～1：10 普通残業 C 1：20～ 深夜残業 作成したい表は 始業時間と 終業時間をいれると A（定時だと2：40） B（定時だと2：50） C と3段階で表示されるものです 何とかできないでしょうか？ フリーソフトも色々みたのですが？ 困っています 宜しくお願いいたします
- ベストアンサー
- その他MS Office製品
- 複雑な勤務時間管理表
最近会社での勤務時間管理方法が変わり、月末タイムカードに自ら残業時間等を記入し提出せねばならず、エクセルで自動計算したいと思ってるんですが、計算項目も多く困っています。 出勤時間と退勤時間を入力するだけで以下の条件をすべて表示できますでしょうか？ 1：当日実働時間 （休憩の1ｈを必ずマイナスしなければなりません） 2：当日残業時間 （実働7.5ｈを超えた場合、1分単位での計算が必要です。7.5時間を下回った場合も1分単位でのマイナス表示が必要です。） 3：深夜勤務時間 （22：00以降の1分単位での計算が必要です。24：00を超える場合も多く、当日深夜勤務にカウントされます。） 4：1～3までの各項目合計 自分で何度かチャレンジしてみたのですが、マイナス表示もある残業時間の計算や深夜勤務の24：00以降が発生した場合の計算等がうまくいきません。 入力項目が増えるのはまったく構いません。 どうかご教授お願いします。
- ベストアンサー
- オフィス系ソフト
お礼
回答ありがとうございます。 完璧でした！ありがとうございました！