エクセルの関数について教えてください。

エクセルで残業時間等の計算のできる関数の組み方を教えてください。
例えばA1セルに「9:00」と入力しB1セルに「23:00」と入力すると、定時の時間が8時間、労働8時間以上でかつ深夜(22:00以降)までの残業時間数(例の場合)4時間、深夜時間(例の場合)1時間というそれぞれの時間数が自動的に出てくるようにするにはどのような関数式を作ればよいでしょうか。ただし時間はまちまちで、必ずしも9:00スタートではないのです。残業時間、深夜時間の規定は一応労働基準法の通りで、労働時間数8時間以上が残業時間、ただし20:00~朝5:00までは深夜時間と考えようと思います。
何卒宜しくお願いいたします。

回答の条件
  • 1人2回まで
  • 登録:2009/12/01 23:43:50
  • 終了:2009/12/08 23:45:02

回答(3件)

id:SALINGER No.2

SALINGER回答回数3454ベストアンサー獲得回数9692009/12/02 00:55:40

ポイント27pt

A1に始業時間、B1に終業時間としたときの各時間の数式です。

  • 定時時間
=IF(B1>A1,IF(HOUR(B1)-HOUR(A1)>8,8,HOUR(B1)-HOUR(A1)),IF(HOUR(B1)+24-HOUR(A1)>8,8,HOUR(B1)+24-HOUR(A1)))

  • 残業時間
=IF(B1>A1,IF(HOUR(B1)-HOUR(A1)>8,HOUR(B1)-HOUR(A1)-8,0),IF(HOUR(B1)+24-HOUR(A1)>8,HOUR(B1)+24-HOUR(A1)-8,0))

  • 深夜時間
=IF(IF(B1>A1,HOUR(B1),HOUR(B1)+24)<=22,0,IF(IF(B1>A1,HOUR(B1),HOUR(B1)+24)<=29,IF(HOUR(A1)>22,IF(B1>A1,HOUR(B1),HOUR(B1)+24)-HOUR(A1),IF(B1>A1,HOUR(B1),HOUR(B1)+24)-22),IF(HOUR(A1)>22,29-HOUR(A1),7)))

格式は時間間隔を表すので、セルの表示形式は標準か数値にしてください。

例えば深夜時間7.5のように時刻と混同しないようにあえて少数にしています。

id:SALINGER No.3

SALINGER回答回数3454ベストアンサー獲得回数9692009/12/02 01:53:12

ポイント26pt

先に回答したのに間違いがありましたので修正します。

  • 定時時間
=IF(B2>=A2,IF((B2-A2)*24>8,8,(B2-A2)*24),IF((B2+1-A2)*24>8,8,(B2+1-A2)*24))

  • 残業時間
=IF(B2>=A2,IF((B2-A2)*24>8,(B2-A2)*24-8,0),IF((B2+1-A2)*24>=8,(B2+1-A2)*24-8,0))

  • 深夜時間
=IF(IF(B2>=A2,B2,B2+1)*24<=22,0,IF(IF(B2>=A2,B2,B2+1)*24<=29,IF(A2*24>22,IF(B2>=A2,B2,B2+1)*24-A2*24,IF(B2>=A2,B2,B2+1)*24-22),IF(A2*24>22,29-A2*24,7)))+IF(B2>=A2,IF(A2*24<5,IF(B2*24<5,(B2-A2)*24,5-A2*24),0),IF(A2*24<5,5-A2*24,0))

各時間は時間間隔なので、時刻と混同しないようにセルの書式設定を標準か数値にしてください。

7.5のように少数で分を表したほうが、使い易いはずです。

ここでは始業時間>終業時間の場合は24時間を超えていると判別しています。

更に、深夜時間の計算として始業時間が5時前の場合を考慮していますので少し長くなりました。

id:Platini

ご回答有難うございます。あつかましい再質問ですが、例えば19:00~7:00までの勤務のような場合、労基法を適用すると19:00~22:00までは通常時給(1.0)で22:00~6:00までは勤務時間が8時間に満たなくても1.25倍になるようなのです。さらに労働時間8時間以上で、かつ深夜時間帯(22:00~6:00)でっすと1.5倍の深夜時給になるようなのですが、こういった複雑な設定は関数でできるでしょうか?あるいは他になにか簡単な方法などないでしょうか?アドバイスいただけるとありがたいです。

2009/12/08 10:00:07
  • id:SALINGER
    >アドバイスいただけるとありがたいです。
    残念ながらこのコメント欄がクローズであり、回答上限2回ではこちらからアドバイスする方法はありませんでした。


    労基法では23時~6時だったと思います。ただし、変形労働時間制を取り労使協定をしている場合はその限りではありませんが。
    それに合わせて出来ないこともないですが、翌日の処理などが絡んできますので簡単な方法は無いと思っていいです。
  • id:SALINGER
    一つの数式で処理するとわかりづらいので、ユーザー定義関数にした方がいいかもしれませんね。
    需要があれば作りますが。
  • id:SALINGER
    ユーザー定義関数を作りましたので、下記トラックバックからどうぞ。

この質問への反応(ブックマークコメント)

トラックバック

  • 労働時間を求める関数 http://q.hatena.ne.jp/1259678627 数式で一発で出す関数だと複雑になってくるのでユーザー定義関数を作ってみる。 使う場合は、以下のコードを標準モジュールにコピーして
「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません