[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『勤怠表の関数について 残業開始時間と深夜開始の出し方』（RISA）
エクセルでタイムカードの集計をする際にタイムカード通りの時刻を打ち込むだけで下記のような時間を出したいと考えています。
A B C D E F G H I
開始時間 終了時間 開始時間 終了時間 休憩 残業開始 残業終了 深夜開始 深夜終了
9:20 26:50 9:30 18:30 1:00 18:30 22:00 22:00 26:50
9:10 21:15 9:30 18:30 1:00 18:30 21:15
9:15 16:10 9:30 16:10 1:00
A列およびB列およびE列・・・手入力
C列・・・CEILINGにて自動計算(30分の切り上げ)
D列・・・8時間以内の労働であればそのままの時刻
8時間を超えるのであれば8時間を超えたときの時刻
F列・・・労働してから8時間が経過した時刻（なければ空白）
G列・・・8時間を超えている場合の労働終了時刻
深夜残業がある場合は22：00
H列・・・深夜残業がある場合のみ 22：00
I列・・・深夜残業がある場合のみ 終了した時間
（深夜時間帯は22：00〜翌5：00まで）
D F G H Iの値を出すための関数を教えていただけないでしょうか？
知恵をお貸しください。
よろしくお願いいたします。
＜ 使用 Excel：Excel2013、使用 OS：Windows7 ＞
こういうこと？
D2 =IF(B2="","",MIN(B2,C2+E2+"8:00"))
F2 =IF(B2=D2,"",D2)
G2 =IF(B2=D2,"",MIN(B2,"22:00"))
H2 =IF(B2-"22:00">0,"22:00"*1,"")
終了時間が「30:00」だったら、I列はどうなればいいのかな？ 29:00？ それとも30:00？
29:00なら I2 =IF(H2="","",MIN(B2,"29:00"))
30:00なら =IF(H2="","",B2) > C列・・・CEILINGにて自動計算(30分の切り上げ)
これ労基法的に問題ないんですかね？ （笑） 2016/02/23(火) 19:54
開始時刻が「16:00」、終了時間が「26:00」のケースはどうなりますか？
もし、そんな勤務形態があり得ないなら、どんな勤務時間帯が今回考慮すべき範囲なのか補足してください。
（半平太） 2016/02/23(火) 19:59
I列は↓を使わせていただきます！
I2 =IF(H2="","",MIN(B2,"29:00"))
開始時刻が「16:00」、終了時間が「26:00」のケースはどうなりますか？ もし、そんな勤務形態があり得ないなら、どんな勤務時間帯が今回考慮すべき範囲なのか補足してください。
遅くても13：30からの出勤なので8時間超える前に深夜に入ることはないと思います。
しかしもしあるとすれば・・・
開始時間 終了時間 休憩 残業開始 残業終了 深夜開始 深夜終了
16:00 25:00 1:00 25:00 26:00 22:00 26:00
になりますかね・・・
労基法的には・・・（笑）
先方からの資料を素早く集計するためのエクセルなので・・・（笑）
（RISA） 2016/02/23(火) 21:05
＞半平太さん、ありがとうございます！
済みません。その回答案は私のじゃありません。
紛らわしいHNですが、(笑)さんと言う回答者のものです。
（半平太） 2016/02/23(火) 22:02
> しかしもしあるとすれば・・・
G2 =IF(B2=D2,"",IF(F2-"22:00">0,B2,MIN(B2,"22:00")))
かな？
例題でしか検証してないので、間違ってたらすみません。 （笑） 2016/02/23(火) 22:27
ありがとうございました！
完成したエクセルに過去分のタイムカードを打ち込んで確認中です！
実はこの時間の横の列に
I J K
通常 残業 深夜
でタイムカード時間を合算しているのですが、週44時間を超えた場合に
残業時間の方にカウントさせる方法はないのでしょうか・・・？
起算曜日は日曜日です。
難しいようであれば諦めます・・・
よろしくおねがいします。
（RISA） 2016/02/25(木) 17:21
具体例を挙げて説明してもらわないと、何のことやらさっぱりわかりません。
I列は「深夜終了」なので、J列からですよね？
> タイムカード時間を合算しているのですが
現状はどうしているのか、その数式も提示してください。 （笑） 2016/02/25(木) 19:24
の間違いでした。すみません。
具体例です↓
A B C D E F G H I J K L
開始時間 終了時間 開始時間 終了時間 休憩 残業開始 残業終了 深夜開始 深夜終了 通常 残業 深夜
9：20 22：04 9：30 18：30 1：00 18：30 22：00 22：00 22：04 8：00 3：30 0：04 ←日曜日
9：20 22：15 9：30 18：30 1：00 18：30 22：00 22：00 22：15 8：00 3：30 0：15 ←月曜日
9：20 22：22 9：30 18：30 1：00 18：30 22：00 22：00 22：22 8：00 3：30 0：22 ←火曜日
9：20 22：12 9：30 18：30 1：00 18：30 22：00 22：00 22：12 8：00 3：30 0：12 ←水曜日
9：20 22：08 9：30 18：30 1：00 18：30 22：00 22：00 22：08 8：00 3：30 0：08 ←木曜日
9：20 22：30 9：30 14：30 1：00 14：30 22：00 22：00 22：30 4：00 7：30 0：30 ←金曜日
週所定44時間の事業所なのでJの通常労働時間が44時間を超えた時間からはすべて残業の方にカウントされるようにしたいのです。
（具体例でいうと金曜日の14：30からです。）
起算日が日曜日というのは日曜日で44時間がリセットされるということです。
現状はどうしているのか、その数式も提示してください。
現状は
J=D2-C2-E2
K=G2-F2
L=I2-H2
このように集計しております。
以上、よろしくお願いします！
（RISA） 2016/02/26(金) 17:21
下の疑問にお答え下さい。
1）このシートは1週だけで完結ですか。 それとも2週、3週･･･と下に続くんですか。 下に続くのなら、この行は何曜日と、どうやって判断するんですか。 どこかに日付があるとか？
2）下に続く場合、出勤してもしなくても必ず1週間分（日〜土）「7行」あるんですか。 それとも出勤した日だけのデータですか。 出勤したのが週に4日なら、その週は4行しかないということですか。
3）下に続く場合、最終的に何週分ぐらいになりますか。
4）開始「16:00」、終了「26:00」の場合 開始 終了 休憩 残始 残終 深始 深終 16:00 25:00 1:00 25:00 26:00 22:00 26:00
↑ のようにしたい、ということでしたよね。 これだと深夜の時間帯が他とダブリますが、それでいいんですか。 それともこんなケースは実際にはない、ということですか。
5）B列の終了時間が29:00を超えることは実際ありえますか。
6）作業列を使ってもいいですか（使わないとメンドーことになりそうな予感） また新たな疑問が出てくると思いますが、とりあえず以上です。 （笑） 2016/02/27(土) 13:06
> 6）作業列を使ってもいいですか（使わないとメンドーことになりそうな予感）
誤）メンドーことになりそうな予感 正）メンドーなことになりそうな予感
一応訂正 「編集」からやろうとするとエラーになってできなかった。 （笑） 2016/02/27(土) 17:05
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.