• ベストアンサー
  • 困ってます

複雑な勤務時間管理表

最近会社での勤務時間管理方法が変わり、月末タイムカードに自ら残業時間等を記入し提出せねばならず、エクセルで自動計算したいと思ってるんですが、計算項目も多く困っています。 出勤時間と退勤時間を入力するだけで以下の条件をすべて表示できますでしょうか? 1:当日実働時間 (休憩の1hを必ずマイナスしなければなりません) 2:当日残業時間 (実働7.5hを超えた場合、1分単位での計算が必要です。7.5時間を下回った場合も1分単位でのマイナス表示が必要です。) 3:深夜勤務時間 (22:00以降の1分単位での計算が必要です。24:00を超える場合も多く、当日深夜勤務にカウントされます。) 4:1~3までの各項目合計 自分で何度かチャレンジしてみたのですが、マイナス表示もある残業時間の計算や深夜勤務の24:00以降が発生した場合の計算等がうまくいきません。 入力項目が増えるのはまったく構いません。 どうかご教授お願いします。

共感・応援の気持ちを伝えよう!

  • 回答数8
  • 閲覧数375
  • ありがとう数2

質問者が選んだベストアンサー

  • ベストアンサー
  • 回答No.5
  • KURUMITO
  • ベストアンサー率42% (1835/4283)

単に今までの理解の範囲でしたら、次のようになるでしょう。 初めにマイナスの時間も発生するとのことなので「Office」ボタンからを「Excellのオプション」をクリックします。「詳細設定」の項目の「次のブックを計算する時」にある「1904年から計算する」にチェックを付けてOKします。 データは2行目からとして、出勤時間はB列、退勤時間はC列、実働時間はD列、残業時間はE列、深夜時間はF列、合計時間はG列とします。 D2セルには次の式を入力します。 =IF(COUNT(B2:C2)=2,C2-B2-"1:00","") E2セルには次の式を入力します。 =IF(COUNT(B2:C2)=2,D2-"7:30","") F2セルには次の式を入力します。 =IF(COUNT(B2:C2)=2,IF(C2>TIMEVALUE("22:00"),C2-"22:00",""),"") G2セルには次の式を入力します。 =IF(COUNT(B2:C2)=2,SUM(D2:F2),"") D2セルからG2セルまでを選択してから下方にオートフィルドラッグします。 なお、最後にB列からG列までを選択して右クリックし、「セルの書式設定」の「表示形式」で「ユーザー定義」として[h]:mmと入力しOKします。

共感・感謝の気持ちを伝えよう!

関連するQ&A

  • 勤務時間について

    勤務時間はタイムカードがある会社では出勤時間から退勤時間まで1分単位で計算するのが原則ということですが、 そうすると30分単位で残業時間を管理している会社は違反なのでしょうか? 実は、タイムカードで出勤と退勤は管理しているのですが、実際は30分単位の日報で残業が管理されていました。手元にタイムカードがない場合はどうやって調べるのでしょうか?会社が保管しているであろう賃金台帳に書いてあるのでしょうか?

  • タイムカードで勤務時間を計算する関数

    タイムカードを作成しているのですが、初心者のため関数がわかりません・・・ どなたかご教授いただけないでしょうか? 以下の「時間内勤務時間」「普通残業時間」「深夜残業時間」に入れるべき関数がわかれば お願いします。 出社時刻 退社時刻 休憩時間 時間内勤務時間 普通残業時間 深夜残業時間 9:00     22:20    0:45   7:45         4:15       0:20  時間内勤務時間・・・9:00~17:30までの勤務時間。休憩時間45分引いて最高で7:45です。 普通残業時間・・・17:30~22:00までの時間。休憩時間15分を引いて最高で4:15です。 深夜残業時間・・・22:00以降の時間です。

  • Excelで勤務時間の計算をしたいです。

    すでに作られている勤務表を前任者から受け継ぎましたが、 すべて手入力しているので関数を使ってもっと効率化を図りたいと思います。 皆様の知恵をお貸し下さい。 出勤時間→B列 退社時間→C列 出勤日数のカウント→D列 残業時間→E列 条件として ・定時は8:00~17:00に対して1時間の休憩をとる。 例:B列   C列  D列  E列   8:00 17:00  1  [空白] ・実働時間9時間を超える場合は残業時間に入れる 例:B列   C列  D列  E列   8:00 18:30  1   1.5 ・実働時間9時間以上の場合は1時間休憩を差し引き実働時間8時間としてそれに対して 出勤日数のカウント「1」 実働時間4時間以下の場合は休憩時間を差し引かずに 出勤日数のカウント「0.5」 実働時間4時間超え、9時間未満のものに対しては休憩時間に関係なく 出勤日数のカウント「1」 (ここが一番厄介な点かと思われます。4時間以内ならカウント0.5、4時間を越えたものはカウント1です。) 例:B列   C列  D列  E列   8:00 12:00  0.5  [空白]   8:00 15:00  1  [空白]   8:00 17:30  1   0.5 ・深夜勤務もあるが、その場合の残業は手動で計算するので、出勤日数のカウントだけでOK 例:B列   C列  D列  E列  23:00  5:00  1  [空白] このような状態なのですが、計算の列などを増やして(あまり増やしたくはないのですが・・・) 関数をいれてある程度自動で計算をさせたいのです。 休憩時間はほぼ1時間と決まっているのであまり列は増やさずに計算させたいです。 簡単な関数は知っていますが、表示形式など詳しいことは分かりません(残業時間1時間半を1.5と表示するなど・・・) ちなみに出勤日数のカウント、残業に関しては1や1.5などの数値を 他の時給計算で使っています。 巧く説明できなくてすみません((汗)) どのような関数を組んだらよいか、ご指導を宜しくお願いします_(._.)_

その他の回答 (7)

  • 回答No.8
  • tom04
  • ベストアンサー率49% (2537/5117)

No.1・7です! 何度もごめんなさい。 No.7の数式で E2セルはあんなに難しくしなくていいみたいです。 =IF(D2="","",IF(B2<="22:00"*1,0,B2-"22:00")) に訂正してください。 どうも何度も失礼しました。m(__)m

共感・感謝の気持ちを伝えよう!

  • 回答No.7
  • tom04
  • ベストアンサー率49% (2537/5117)

こんばんは! No.1です。 補足を読ませてもらうと、単純なことではなかったのですね? 参考になるかどうか判りませんが、↓の画像で説明させていただきます。 ただ、気になるのは通常の出社時刻や退社時刻(定時)は考えないのですね? そして、もし、出社時刻が23:00でも必ず休憩の1時間は引くということでの回答になります。 表のC2セルに =IF(COUNTBLANK(A2:B2)>0,"","24:00"-A2-"1:00") D2セルに =IF(C2="","",C2-"7:30") E2セルに =IF(D2="","",IF(AND(B2>="22:00"*1,B2<="24:00"*1),B2-"22:00",IF(B2>"24:00"*1,B2-"22:00",0))) という数式を入れ、C2~E2セルを範囲指定した後に、 E2セルのフィルハンドルで下へコピーしています。 尚、前回はセルの表示形式で[hh]:mm と回答しましたが、 時間部分が一桁の場合「0」が表示されて目障りのようなので [h]:mm としてみてください。 以上、参考になれば幸いですが、 今回も的外れならごめんなさいね。m(__)m

共感・感謝の気持ちを伝えよう!

質問者からのお礼

お返事遅くなり申し訳ありません。 画像付きのわかりやすい回答ありがとうございます! 他の回答と共に是非参考にさせていただきます! 大変たすかりました~!

  • 回答No.6
  • KURUMITO
  • ベストアンサー率42% (1835/4283)

解答No5です。 何時間何分のように表示するのでしたらB列からG列を選んで右クリックし、セルの表示形式のユーザー定義では[h]"時間"mm"分"と入力してOKします。

共感・感謝の気持ちを伝えよう!

質問者からのお礼

お返事遅くなり申し訳ありません。 大変参考になりました。 素早いご回答、大変助かりました! ありがとうございました!!

  • 回答No.4

残業時間の表示形式は、決まっているのでしょうか? たとえば、-3時間30分の場合、-210分と表記するのはNGですか?

共感・感謝の気持ちを伝えよう!

質問者からの補足

ご回答ありがとうございます。 NGではありませんが、日単位・月単位共に最終的な提出は ”3時間12分”26時間48分”の様に1分単位での表記が必要になるので、可能であれば時間と分は別に表示させたいと思っております。 よろしくお願いします。

  • 回答No.3

出勤時刻と退勤時刻に、制限は無いのでしょうか? たとえば、出勤時刻が19時00分、退勤時刻が24時00分、という入力は有り得るのでしょうか? 有り得るのであれば、その場合の、当日実働時間、当日残業時間、深夜勤務時間、のそれぞれは、いくつになるのが正しいのでしょうか? 有り得ないのであれば、なぜ有り得ないのか、という条件を提示してください。

共感・感謝の気持ちを伝えよう!

質問者からの補足

ご回答ありがとうございます。 出勤時間19:00 退勤時間24:00 は有り得ます。 この場合の各入力時間は ・当日実働時間 4時間00分(休憩1hを引いてます) ・当日残業時間 -3時間30分 (所定労働時間7h30分に満たない時間数です) ・深夜勤務時間 22:00以降の2時間00分 となります。 よろしくお願いします。

  • 回答No.2
  • KURUMITO
  • ベストアンサー率42% (1835/4283)

残業時間にマイナスとはどのような意味でしょう。マイナスなら残業時間は無いということではないのですか?

共感・感謝の気持ちを伝えよう!

質問者からの補足

ご回答ありがとうございます。 月の残業時間は(実働時間)-(所定労働時間)でカウントされる為、 月初めに残業が重なった場合実働7.5hに満たない時間で退勤し、 残業時間計よりマイナスされます。 ですので日単位でのカウントにはマイナスの残業が発生します。 説明不足で申し訳ありません。

  • 回答No.1
  • tom04
  • ベストアンサー率49% (2537/5117)

こんにちは! 参考になるかどうかわかりませんが・・・ 今、職場からなので(見積の最中です) 取り急ぎ24:00以降の計算方法の場合のみ 回答させていただきます。 24:00以降の入力方法を、たとえば午前2時20分であれば 26:20 のように入力することが前提になります。 まず、セルの書式設定から表示形式を ユーザー定義 → [hh]:mm としておいてください。 そして、メニュー → ツール → オプション → 計算方法タブで 「1904年から計算する」にチェックを入れます。 これで普通に足し算・引き算は問題なく出来て、マイナスも表示されるはずです。 以上、当方使用のExcel2003での回答でしたが、 お役に立てれば幸いです。m(__)m

共感・感謝の気持ちを伝えよう!

質問者からの補足

ご回答ありがとうございます。 24時以降の計算のためにユーザー定義もご指摘の通りに、 計算方法タブも1904年にチェックを入れた状態で作成しておりました。 何が問題なのかもわかってない状況なのです。 自分でも調べ続けますが、もしよければさらなるご回答よろしくお願いします。

関連するQ&A

  • 深夜勤務時間の求め方を教えてください。

    深夜勤務時間の求め方を教えてください。 Office2003を使っています。 エクセルで出勤表を作っているのですが、深夜勤務時間数の算出がうまくできません。 超勤時間は、17:30~22:00 深夜時間は、22:00~29:00 となっています。 セルK5に退社時刻を入力し、 その際に、 セルO5に、超勤時間数、 セルP5に、深夜時間数が計算されるように設定したいのです。 例えば、残業時間が17:30~30:00までの場合、 セルP5には、“7:00” と入るようにしたいのですが、どうしたら良いでしょうか。 よろしくお願いします。

  • タイムカードの残業時間計算で、こんな残業時間計算に対応したものはありますか?

    タイムカードの計算について、 私の会社では、勤務時間内に出勤・退社した場合、残業時間からマイナスする、という形態をとっています。 従業員全員の勤務時間を一律全く同じにすることができない仕事のためです。 弥生給与を試用版で試してみましたが、 タイムカードを入力するとき、勤務時間外の残業時間の計算はしてくれますが、勤務時間内の出勤・退社した場合は残業時間のマイナスとしては計算はしてくれません。 勤務時間内に出勤・退社した場合、残業時間のマイナスとして計算してくれる給与計算ソフト、ないしエクセルのフォームを知っている方がおりましたら、是非お教えください。 よろしくお願い申し上げます。

  • エクセルで勤務時間管理表

    エクセルで勤務時間管理表を作成しました。 D列に出勤時刻、E列に退勤時刻とし、G列には労働時間が4時間を超える場合に1時間マイナスをして表示させるように計算式をいれました。 休日は未入力、空欄です。 これでG列をオートSUMにて月間労働時間表示させると休日の分も1時間マイナスしてしまいます。 どのようにすればいいのでしょう?

  • 勤務時間に対して残業時間の割合

    勤務時間に対して、残業時間がどれぐらい多いのか知りたい時に、 どのような計算方法がありますか?

  • Excelで勤務時間を管理する表を作成したいのですが

    ExcelはSUMくらいしか使用していない、初心者です。 1ヶ月単位の勤務時間を管理するExcelの表を作りたいと思っています。 1ヶ月の所定勤務時間が150時間で、 実際勤務時間が140時間30分の場合、 差引勤務時間が9時間30分となる。 という単純な表を作成したいのですが、 140時間30分という時間を140:30と表示できません。 140:30と入力すると、140:30:00となってしまします。 差引勤務時間がマイナスになるとき (実際勤務時間が所定勤務時間を超えた場合)には 時間の前に-(マイナス)をつけたい(もしくは赤字で表示したい)のですが、 どういう式を入力すればいいのでしょうか。 前月の超過分(過少分のときもある)と今月の差引勤務時間を計算して あとどれくらい超過(過少)なのか算出したいのです。 すぐできると軽く考えていたのですが、なかなか難しくて。 どうぞよろしくお願いいたします。

  • excel関数で勤務時間の計算をしたいと思っています。

    excel関数で勤務時間の計算をしたいと思っています。 添付の画像の通り、実際の勤務実績を入力すると、右側の色つき部に早朝、早出や残業時間などを自動で表示できないかと考えています。 何とかなりませんでしょうか?

  • 勤務時間について

    ハローワークで目にした求人に勤務時間が9時~19時となっている会社があったんですが休憩時間一時間を除いても単純計算で9時間労働になりますがこれって労働法違反ではないんでしょうか?普通残業時間抜きで基本的に8時間労働が普通ですよね~??残業代支給となっていますがこれって残業時間込みで19時と判断すればいいのでしょうか?もしくはこれプラス残業有りと判断するのが妥当でしょうか?もしそうであるなら一時間残業で10時間労働ってことですよね~。 ちょっと長いのでは?と思ったんですが・・・。

  • 勤務時間の証拠になりますか?

    現在勤めている会社なのですが、 タイムカードやIDカードなどはなく出勤は出勤簿に印鑑を押しています。 時間の記入はないので、遅刻をした場合などかなり曖昧になっており、残業時間についてもあやふやになっています。 (すべて自己申告なのでごまかしているひともちらほら・・) そこで、自分なりの勤務時間表をつけていこうと思っているのですが、 ノートに日時と勤務時間を記入していくものはきちんとした証拠になるのでしょうか? 日記等に記していくのは有効だと他の質問で見かけたのですが、 ただ単に時間を書き留めたものでは公に証拠とは認められないのでしょうか?

  • 勤務時間管理

    最近になって、勤務時間の計算方法が変わりました。 (1)一ヵ月のシフト内において ・所定労働時間が1日毎に指定されます。 (日毎に、8:00-18:00、9:00-20:00などと記載されています) ・休日も指定されています。 (入社したときは、月9回と言われていますが、大体6日&#65374;7日がほとんどです) (2)1日の所定労働について ・(1)で指定された時間を超えると残業になります。 (但し、所定時間に満たない場合は、月末に残業時間とあわせて相殺されます) ---------------- 質問は、 ・そもそも8Hを超えた場合、残業(25%増)としての扱いになるのではないでしょうか? ⇒つまり、シフトで毎日10時間を所定されても、残業になりません。 ・休日は、指定される日数だけで、休日出勤(時間外)として扱われません。これはダメではないでしょうか? この点を会社に確認をしたら、社労士から問題ないといわれたそうです。が、何か腑におちません。 ---------------- 宜しくお願いします。

  • Excelで勤務表を作りたいのですが

    Excelで勤務表を作りたいのですが、なかなか関数がうまく使えません。 時間計算をご存知の方、教えて下さい。 入力列 A:出勤時刻 B:退勤時刻 C:勤務開始時間 *始業9:00のため、9時より以前の出勤時刻は9:00、9:00以降の出勤はその時刻を表示する。 D:休憩時間 E:実働時間(始:C/終:B/マイナス:D) F:時間内勤務(8時間まで) G:時間外勤務(8時間を超過したところから・22時迄) H:深夜残業(22時以降の勤務時間) このような説明でお分かり頂けるでしょうか。 よろしくお願いします。