エクセル出勤簿、定時労働時間、時間外、休日労働時間

はじめまして。似た内容のものはあたってみたのですが、応用することが出来ませんでした。
出勤日、出退勤時刻、各休憩時間を入力することで、定時労働時間、時間外労働時間、深夜、休日の労働時間を出力したいと思っています。
土日休日です。

Q1.定時労働時間欄の計算の仕方
Q2.時間外労働時間欄の計算の仕方
Q3.深夜労働時間欄の計算の仕方
Q4.休日出勤欄の計算の仕方

それぞれやってみたのですが、どうも乱雑になってしまってうまく計算できませんでした。
良い書き方あれば教えていただけないでしょうか？

添付画像の黄色は自分で入力、水灰色は自動で表示させる部分です。

No.5 回答者： satoron666 回答日時： 2014/01/27 08:48

画像が小さく、どのような書式で入力されているのか

良く分かりません…

できたら、文章で、計算式？
定時労働時間欄の計算の仕方などを文章で教えて頂きたいです。
何時から何時までが、定時労働時間で・・・という感じに。

そうして、例も文章化して頂けると、お手伝いできるかもしれないです。

No.4 回答者： bunjii 回答日時： 2013/08/19 19:28

>退勤時刻の問題は、24時以降、25時、26時等と入力することで対応するつもりです。

定時の9時間拘束、実働8時間については次の式で対応できるはずです、

=IF(OR(D2="",E2=""),"",MIN(E2-D2,TIME(9,0,0)-F2))

D2に出社時刻を入力し、E2に退社時刻を入力します。
F2は定時の休憩時間とし、手入力との条件にしてあります。
D2またはE2がブランクの場合は計算を行いません。
MIN関数で2つのパラメータの内小さい値が採用されます。
TIME関数で9時0分0秒を定数で使っているのは拘束時間の9時間を意味します。
拘束時間に満たない場合は定時の勤務となります。
8時間以上の拘束では強制的に1時間の休憩を入れないと論理に合わないので合うように修正してください。

時間外の勤務時間は全拘束所間から定時の時間と休憩時間を差し引けば算出できます。
フレックスタイム制とのことであり何時までは早出、何時以降は残業とする必要はないでしょう。

深夜勤務の時間帯は時間外勤務の時間帯と重なりますが賃金計算で深夜割増が必要な時間を算出すれば良いので残業+深夜の時間帯を考えなくて良いと思います。
計算方法は午前の部と午後の部を加算する方法が分かり易いと思います。

午前の部は以下の算式で良いはずです。

=MAX(TIME(5,0,0)-D2,0)+MIN(MOD(E2,1)*INT(E2),TIME(5,0,0))

IF関数を省いていますので、必要に応じで追加してください。
複雑な部分は24時を超えた時間帯を午前の部に加えているところです。
夕方に出勤し、翌日退社することを考慮しました。

午後の部は以下の式で算出できるはずです。

=MAX(INT(E2)-TIME(22,0,0),MOD(E2,1)-TIME(22,0,0),0)

24時以降に退社するときは計算の都合上、午前の部の式に加算されています。
尚、退社が24時を超えるときの入力方法は25:00等の書き方に無理があるので別の方法にされることをお勧めします。
24時間を超える勤務がありえなければ出社時刻より退社時刻が小さいときは翌日に跨っていると判断する方法で対応できます。

これだけのアドバイスで殆ど完成するはずです。
後はあなたの理解力と応用力で対処してください。
計算式のタイプミスは無いと思いますが思い通りの動作にならないときは再度個別案件として質問してください。
トータルシステムとしての指導は説明が長くなり記述しきれません。

No.3 回答者： bunjii 回答日時： 2013/08/18 22:45

ボランティアのアドバイスで対処できる範囲を超えています。

考え方について提示させていただきます。

>勤務8時間を超える部分で22時～05時にかかる部分-深夜休憩時間を深夜残業時間
翌日に跨る勤務のとき退社時刻-出社時刻では正常に計算ができませんので、日付の要素を追加する必要があります。
24時間以上の連続勤務が無いものとしないと関数式が複雑になり挫折の原因になります。

>出社時間は自由ですが、出勤簿上の定時は08時から17時までです。
フレックスタイム制のようですが、1日の拘束時間が9時間に満たない部分は月間で調整する必要が有るので毎日の勤務時間で時間外を算出するのは不合理です。

>出来たのは曜日と休日フラグだけでした。
土日の休日は年月日から判定できますが祝祭日および特別休日は手動入力が必要と思います。

その他、取り組みが進めば更に問題点が出ると思います。
やはり、業務システムの専門家と相談すべきです。

この回答への補足

回答有難うございます。

退勤時刻の問題は、24時以降、25時、26時等と入力することで対応するつもりです。
24時間以上の連続勤務はありません。
万が一あった場合の措置も、05時出勤28時59分（朝4時59分）退勤として一旦処理することで簡素化できると思います。
出勤簿上処理できても、労働問題的に社内で問題になると思うのでまず無いと思いますが、万が一あったら手入力で補正処理するでしょう。
頻度的に考えて、そのレベルの対応で十分かもしれません。

【結論】
早朝出勤は多いですが、殆どの場合深夜残業時間まで(これまでの平均で19時ごろ)には終業となるため、とりあえずの間は自動計算せずに手動入力とする方法で対応してみるつもりです。

なお、祝祭日とその振替休日は計算式で算出できるので問題なく使用していました。(別会社に所属していた時に使用してました)
やり方はここで説明するより「国民の休日　エクセル」などで検索されればそのほうが早いでしょう。
時折、祝日の日の法改正によって計算方法が変わる場合があるので注意した方がいいですね。

専門家についてですが、このエクセルの目的は会社側の給与計算のチェックに個人的に対応するためのものなので、相談することはないのではないかと思います。
状況の小出しになってしまってすみません。

以上、ありがとうございました。

補足日時：2013/08/18 23:21

No.2 回答者： bunjii 回答日時： 2013/08/17 21:26

Q1.定時労働時間欄の計算の仕方

Ａ１計算の仕方を提示して頂いてＥｘｃｅｌでの計算式を提示することはできます。
定刻出勤制、フレックスタイム制、球形の取り方等の運用形態で定時勤務の算出が異なります。

Q2.時間外労働時間欄の計算の仕方
A2これも運用基準の提示が無いと計算式を考えられません。
就業規則との関係もありますので社内でシステムを構築するか、専門業者（プログラマ）に委託すべきです。

Q3.深夜労働時間欄の計算の仕方
A3前項に同じです。

Q4.休日出勤欄の計算の仕方
A4これも勤務カレンダーが社内で独自に決められていると思いますので第三者が提案できるようなことにはなりません。

計算する基準の提示が無ければシートのレイアウトや計算式の提示はできません。

>添付画像の黄色は自分で入力、水灰色は自動で表示させる部分です。
細かくて読めません。
拡大すると文字が潰れて判読が困難です。
この画像を元に勝手解釈のシートを作るのは無理でしょう。

営利事業に伴う事務処理を無料で手伝わせようとしても相手にされませんよ。

この回答への補足

基準が不明確でした。すみません。

出社時間は自由で、納期（当日中）に決められた仕事さえ完了すればほぼオッケーな会社です。
出社時間は朝3時の時もあれば朝9時の時もあります。前日までに決まります。
休憩時間も業務の合間を見つけてそれぞれ休憩を取るので、日報には細かく記入はしますが、エクセルの出勤簿には、定時、時間外、深夜それぞれの休憩時間欄に合計の休憩時間を入力して済まそうと思います。

要は
勤務8時間を超える部分で22時～05時にかかる部分-深夜休憩時間を深夜残業時間
勤務8時間を超える部分で22時～05時にかからない部分-時間外休憩時間を通常残業時間
と休日フラグによって表示欄の切り替え
を自動計算させようとしているところです。
なお、出社時間は自由ですが、出勤簿上の定時は08時から17時までです。

出勤簿は1ファイル1社員分で作っています。

*画像が不明瞭ですみませんでした。直し方わかりませんでしたのでそのままです。
代わりに下記の通り、入力欄と表示欄の列を記入してみます。

日付,曜日,休日フラグ,出勤時刻,退勤時刻,休憩時間(定時,時間外,深夜),定時労働時間,時間外労働時間,深夜労働時間,休日労働時間(08:00～16:59,05:00～07:59＆17:00～21:59,22:00～28:59)

入力は日付、出退勤時間、各休憩時間を入力することでそれ以外の欄が自動で埋まるよう検討しています。
出来たのは曜日と休日フラグだけでした。もう少し頑張ってみます。

御指摘ありがとうございました。

補足日時：2013/08/18 08:29

No.1 回答者： aokii 回答日時： 2013/08/17 16:00

勤務時間の計算(Calculation of working hours)

https://sites.google.com/site/kinnmujikannnokeis …

この回答へのお礼

参考資料の提示、ありがとうございました。
しかし、上にも書き足したとおり、出勤時間、休憩時間などが自由なため、かなり手直しがいることがわかりました。

ただ、今回作成中の出勤簿にも参考になるところはいくつかありましたので、計算例・解説シートの内容を参考にさせていただきたいと思います。

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

お礼日時：2013/08/18 08:35