[[20031011145950]] 『日付をまたぐ時間計算は不可能でしょうか』(しわおじ) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『日付をまたぐ時間計算は不可能でしょうか』(しわおじ)

時間計算の限界?

日報の集計にお世話になりました。
その後、自分なりに集計シートを作成しほぼ完成?を迎えましたが・・・
最後の最後にトラブりました

具体的に申しますと…
0:00から9:00
8:30から17:30
16:30から1:30
などの3交代勤務を例にすると

0:00からの計算で始業時間以前に早出すると日付が前日にまたぎうまくできません。
また3番目の1:30の場合は大丈夫ですが、

別の現場で0:00終業の現場があり
残業になると計算が狂いました(日付が変わるためと思いますが…)

全ての現場でひとつの計算を行う為問題が山積みでした。
エクセルでは日付のまたぐ時間計算は不可能でしょうか?

私は
=IF(Sheet2!D7="","",VLOOKUP(Sheet2!D7,A7:Q104,2,FALSE))
などを多用して複数の工場の勤務シフト・始業・終業時間に対応させましたが、
あとは日付をまたぐ計算の障害を克服するのみ!
なんとか方法はないものでしょうか?

シートの内容は複雑なので必要であれば部分部分で掲載したいと思いますが
打つ手立てはありますでしょうか?

分かる方がおりましたらお願いします


 16:30〜1:30と入力してもエクセルで日付が変わった事が分かりません。
このまま、就業時間ー始業時間としても#####となってしまいます。ですからA1に始業時間、B1に就業時間を入力するとして
 =IF(A1>B1,B1+1-A1,B1-A1)とか=(A1>B1)+B1-A1とかで1を足す必要があると思われます。
(ケン)

 終了時間を1:00であれば25:00、3:00であれば27:00と入力することは可能でしょうか?
 これなら見た目は1:00と3:00ですが、実数は25:00と27:00なので、計算のエラーにも
 なりません。
 もし入力できないようでしたら、終了時間が8:00より小さければ24:00を加算するIF文を
 付け加えれば良いと思います。(スー)

各現場を回る身なのでレス遅れてすみません…
皆さんのご指導感謝しています。
本題ですが、
現場側でのシートが全て12時間表示になっているため現状からでは難しいです>(スー)さま
それと仮に事務員の手を煩わせて強行突破しても0:00よりも前から早出の場合エクセルでは計算していただけないのです…(泣)シフト勤務では3日にまたぐ計算をひとつのシートで計算させるのは難しいのでしょうか?

 勤務表の時間入力のレイアウトはどのようになっているのでしょうか?
 開始時間〜終了時間が1行であれば、3日にまたがったという判断はどこで付くのでしょうか?
 日付で行が決まっていて仮に開始時間が1行目として、終了時間は3行目ということでしょうか?
 そのような形であれば、日付のチェックを行い、同じ日付でないときは空白行×24時間を終了時間に
 加算するというようなIF文を付け加えることもできると思うのですが・・・・・
 12時間表示ということは「AM」「PM」が付いているのでしょうか?(スー)

私の説明が間違っていたようです。すみませんでした…

具体的に説明すると、

まず別シートに 氏名、勤務地、シフト毎の始業時間、終業時間、休憩時間などをリストにして
IF、VLOOKUP、でそれらを入力シートで名前で参照させて、さらに日毎に0〜3までの数字で
シフトを同じように参照させています。

入力するシートはまず上段に別シートから参照するセルを作成しました。

例 A B  C    D    E      F

1氏名 @直 始業時間 終業時間 休憩開始時間 休憩終了時間

2   A直 始業時間 終業時間 休憩開始時間 休憩終了時間

3   B直 始業時間 終業時間 休憩開始時間 休憩終了時間

4   0直 休み用 休み用 休み用 休み用

以下から入力欄

 A  B   C        D        E        F

6氏名 入力セル 

7日付 直入力 始業時間入力セル 始業時間参照セル 就業時間入力セル 終業時間参照セル

 G    H      I    J      K      L

7休憩時間 遅刻早退時間 残業時間 所定労働時間 休憩開始時間 休憩終了時間

上記のような感じで

入力シートのB6に氏名を入力するとリストシートから参照し、A1からF4までを参照します
その後日付毎にB7以降に直を入力するとD7,F7,K7,L7が自動で参照させて
あとはC7,E7を入力すれば足したり引いたりして計算してくれるようにしました。
その際に、入力する時間の表示が0:00から23:59までで各職場統一が取引先共通なので
こちら側で戻す(24:00以降)のが大変なので、15:00から0:00とかの入力にしたり0:00から9:00としているのですが15:00から0:00の場合は0:00以降残業すると翌日になってしまいますし、0:00から9:00の早出も前日の23:00とかからになってしまいます。7以降は1日から31日までの表になっています。

こんな感じで伝わりますでしょうか?お手数おかけします。>(スー)さま


 入力として@シフトのとき、2日始業時間22:00だと2時間「早出残業」で
 終了時間がなくて、3日の始業時間がなくて終了時間が7:00というように
 24時間以上の勤務時間はありえるのでしょうか?
 これだと33時間勤務になると思うのですが・・・(スー)

基本的に8時間勤務なので、
行毎に上記の項目があり7列目以降は1日から31日までとなって、最下段に集計となっています。

例として、(スー)さまご指摘のような場合は基本的にはありません。…が一日に昼勤と夜勤を同時に行う場合はないとはいえませんが、その場合は翌日が休みの場合が多いので次の日に記入するなどで対応します。

10月3日の勤務が3直目で早出で10月3日の23時から10月4日の9時とか10月4日の0時から朝9時の定時は10月3日の列に入力します。

また、10月3日の2直で15時から10月4日の0時に終了の場合、それよりも早退で3日以内にあがる場合や残業で4日の3時や4時の場合も10月3日の列入力になります。

一人に対して1シートなので、最高でも16時間勤務以上は考えられませんし、またいだ日付も2日がいいところでしょうが、セルに数式を入力する場合はBの行での入力により、前日とまたぐ場合や翌日とまたぐ場合があり、複雑になりわからなくなってしまいます。

そのような状況から、「3日をまたぐ時間の計算がわからない」との発言になりました。

無知なものでご迷惑をおかけします。>(スー)さま


 残業時間は、始業時間が参照始業時間以前のときの時間差と終了時間が参照終了時間以降のときの
 時間差を加算したものと思いちょっと強引かとも思うのですが、考えて見ました。
 いろいろな時間を入力して確認はしたのですが、パターンが少なかったかも知れませんので、
 実データで正解がでるかどうか自信はありませんが、残業時間の7行目に貼り付けて見て下さい。
 =IF(OR(B7="A",B7="B"),IF(C7<D7,D7-C7,0),IF(AND(C7>D7,C7<F7),0,D7+24-C7))+IF(AND(B7="@",E7>F7),E7-F7,IF(AND(B7="A",E7<F7,E7<D7),24-F7+E7,IF(AND(B7="B",E7<D7,E7>F7),E7-F7,0)))
 こんな複雑になってしまいましたが、一度試してみていただけますか?
 遅刻早退は逆に始業時間と終了時間のマイナス時間差の合計になると思うのですが、
 上の数式の応用でできるような気もしますが今日はそこまで考えれませんでした。(スー)

 昨日の数式は「@ABの早出残業の条件+@ABの時間外残業の条件」の形で考えたので、
 複雑になってしまいました。
 今「IF(@の早出残業+時間外残業,IF(Aの早出残業+時間外残業,IF(Bの早出残業+時間外残業,0)))」の形で
 考えております。
 もしできあがれば少しはスッキリするのではないかと思いますが、パターンを確認するのに
 時間がかかっております。
 もし計算の考え方が違っているときにはお知らせ下さい。(スー)

 疑問が出てきました。
 例えば極端な時間で、@勤務のとき終了時間が5:00としたとき、早退なのか残業なのか?
 A勤務のとき開始時間が16:30としたき、早出なのか遅刻なのか?
 また終了時間が1:30としたとき、早退なのか残業なのか、何を基準に判断すればよいのでしょうか?
 それによって判断文がちがってくると思うのです。
 基本時間の前後3時間程度が普通で、こんな勤務はありえないと思いますが・・・
 昨日の数式は@0:00〜9:00A8:30〜17:30B16:30〜1:30で考えたのですが、
 途中の文をよくみると、0:00〜はB、16:30〜がA、8:30〜が@だったようですね?
 15:30〜翌日10:00まで連続出勤した場合、早出=1時間・残業=8時間30分となり
 早出残業に9:30と出ればよいのでしょうか?(休憩は無視して)
 この考え方で作成して見ました。
 遅刻早退のセルに =IF(B7="@",IF(C7>D7,C7-D7,0)+IF(AND(E7<F7,E7>D7),F7-E7,0),0)+IF(B7="A",IF(C7>D7,C7-D7,0)+IF(E7<F7,F7-E7,IF(E7>D7,F7+24-E7,0)))+IF(B7="B",IF(AND(C7>D7,C7<F7),C7-D7,0)+IF(E7<F7,F7-E7,0))
 早出残業のセルに =IF(B7="@",IF(C7<D7,D7-C7,0)+IF(E7>F7,E7-F7,IF(E7<D7,E7+24-F7,0)),0)+IF(B7="A",IF(C7<D7,D7-C7,0)+IF(AND(E7>F7,E7<D7),E7-F7,0),0)+IF(B7="B",IF(AND(C7>D7,C7>F7),24-C7+D7,0)+IF(E7>F7,E7-F7,0))
 一応私のなかでは正解がでているのですが・・・(スー)

 ギフトシーズンに突入し現場の直行直帰が増えたため返信が大変遅れて失礼しました。

 まず、疑問に対しての答えが@は早退で、Aは通常出勤となります。

 >途中の文をよくみると、0:00〜はB、16:30〜がA、8:30〜が@だったようですね?

 上記の判断が正解になります。

 >早出残業に9:30と出ればよいのでしょうか

 残業の計算式は上記の方法で正しいと思うのですが、

 A勤務シフトの場合
 一、16:30に出勤して0:00に早退の場合
   出勤時間のセルに16:30とし、退勤時間のセルに0:00の入力
 二、16:30に出勤して2:00まで残業の場合
   出勤時間のセルに16:30とし、退勤時間のセルに2:00の入力

 B勤務シフトの場合
 一、0:00に出勤して9:00に通常出社の場合
   出勤時間のセルに0:00とし、退勤時間のセルに9:00の入力
 二、22:30に出勤して9:00まで早出残業の場合
   出勤時間のセルに22:30とし、退勤時間のセルに9:00の入力

 上記の4例でも機能してくれるのでしょうか?

 事務員と取引先のわがまま(泣笑)で入力は0:00から23:59という形式になっていた 為24時を超える入力ができず、残業の計算が私の中では崩れ去りました。

 おまけに前日からの早出とかで脳みそが溶け出す思いでした・・・

 早々、、、といっても二週間はまだ現場なので(スー)様の上記の方法を実行し疑問が出たら 御返信したいと思います。


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.