度々申し訳ありません。
完璧に出来たと思っていましたらまた問題が出てしまいました。
昨日http://oshiete.goo.ne.jp/qa/8167846.htmlで質問した者です。
早出 5:00~8:50
定時 8:50~17:20
普通残業 17:20~22:00
深夜 22:00~5:00
C D E F G H I
9 出社 退社 昼休憩 夕方休憩 早出残業 普通残業 普通残業内休憩
J K L
9 深夜残業 深夜残業内休憩 実労時間
G9セル =IF(ISBLANK(C9),"",IF(COUNT(C9:D9)<>2,"",MIN(D9,"8:50")-MIN(C9,"8:50"))*24)
H9セル =IF(ISBLANK(C9),"",IF(O9="休日出勤",MIN(D9,"21:59")-C9,MAX("17:20",MIN(D9,"21:59"))-"17:20")*24)
J9セル =IF(D9<=TIME(21,59,0),0,(D9-"21:59")*24)
L9セル =(TIMEVALUE(TEXT((1+D9)-C9,"h:mm"))-TIME(0,E9,0)-TIME(0,F9,0)-TIME(0,I9,0)-TIME(0,K9,0))*24
という数式が入ってます。
O9セルが休日出勤入力欄です。
休憩時間は分単位(例えば45/分)で入力してもらうようにしました。
上記で作成したところ、通常出勤に於いては問題ないのですが、休日出勤として8:50より前に早出出勤した場合、早出残業欄にも早出分が表示されてしまい、普通残業分にも含まれる為重複してしまいます。
例えば8:20出社 17:05退社 昼休憩60分 と入力すると、早出残0.50 普通残8.75 実労7.75 と出てしまいました。
O9セルに休日出勤と入力した場合、早出残0.50を出ないようにし、普通残は昼休憩を差し引いた7.75にすることは出来るでしょうか?
夕方休憩、普通残業内休憩は表の下部で残業合計から差し引くようにしてますので考慮しなくて大丈夫です。
ただ、休日出勤でない通常勤務の際は普通残業から昼休憩を引かれてはおかしくなってしまいますので、その辺りも検討頂けると有り難いです。
色々とややこしくて申し訳ありませんが、お力を貸して頂けると有り難いです。
説明不足ありましたらご指摘ください。
No.2ベストアンサー
- 回答日時:
「退社」の次に「通常勤務」の列を追加し、以降1列ずつずれて
C列…出社(時刻を入力)
D列…退社(時刻を入力)
E列…通常勤務
F列…昼休憩(分を入力)
G列…夕方休憩(分を入力)
H列…早出残業
I列…普通残業
J列…普通残業内休憩(分を入力)
K列…深夜残業
L列…深夜残業内休憩(分を入力)
M列…実働時間
P列…休日出勤の場合に"休日出勤"と入力
とします。
Q~T列を作業列とします。
Q9には
=IF(COUNT(C9:D9)=2,MAX(MIN(IF(C9>D9,D9+1,D9),"8:50"*1)-MAX(C9,"5:00"*1),0),0)
R9には
=IF(COUNT(C9:D9)=2,MAX(MIN(IF(C9>D9,D9+1,D9),"17:20"*1)-MAX(C9,"8:50"*1),0),0)
S9には
=IF(COUNT(C9:D9)=2,MAX(MIN(IF(C9>D9,D9+1,D9),"22:00"*1)-MAX(C9,"17:20"*1),0),0)
T9には
=IF(COUNT(C9:D9)=2,MAX(MIN(IF(C9>D9,D9+1,D9),"5:00"*1+1)-MAX(C9,"22:00"*1),0)+MAX((MIN(D9,"5:00"*1)-MIN(C9,"5:00"*1)),0),0)
と入力します。
これで休憩時間や休日出勤等関係なく、純粋に出社時刻と退社時刻から計算した早出・定時・残業・深夜の時間数がQ~T列に入ると思います。
このQ~T列の時間数から、P列の値("休日出勤")に基づいて条件分岐しつつ、休憩時間を差し引く計算をします。
E9には
=FLOOR(IF(P9="休日出勤",0,R9-F9/1440),"0:15")*24
H9には
=FLOOR(IF(P9="休日出勤",0,Q9),"0:15")*24
I9には
=FLOOR(IF(P9="休日出勤",Q9+R9+S9-(F9+G9+J9)/1440,S9-(G9+J9)/1440),"0:15")*24
K9には
=FLOOR(T9-L9/1440,"0:15")*24
M9には
=E9+H9+I9+K9
と入力します。
夕方休憩と普通残業内休憩は考慮しなくてよいと書かれていますが、一応普通残業から差し引くようにしています。
それと勝手ながら15分単位で切り捨てる処理も含めています。
ありがとうございます。
初めからこのして聞いた方が早かったですね。
極力自分で何とかしようとネットで調べて色々やりましたが、計算式が長くなるにつれて訳が分からなくなり、混乱してしまいました。
教えて頂いたものを元に作りつつ、曖昧な関数の知識もちゃんとした知識になるよう勉強しようと思います。
ありがとうございました。
No.1
- 回答日時:
>早出残業欄にも早出分が表示されてしまい、普通残業分にも含まれる為重複してしまいます。
はい,早出残業を別枠で計算しているというご説明はありませんでしたが,先の回答ではその旨指摘をしておきました。
休日の8:50以前を「早出枠」で計算するのか「残業枠」で計算するのかが不明でしたが,
>O9セルに休日出勤と入力した場合、早出残0.50を出ないようにし、普通残は昼休憩を差し引いた7.75にする
ということで「休日出勤では全部残業枠にしたい(早出には数えない)」という事で良いのでしたら,普通残の計算は元の式マイナスIF(休日は,昼休み時間数,そうでないならゼロ)とでもしておいて,
G9:
=IF(O9="休日出勤",0,今の早出の式)
としておきます。
#補足
今の表組みに囚われているため,段々とこのように継ぎ接ぎ継ぎ足しになっていきます。
たとえば
平日早出
平日遅刻
平日通常勤務時間
平日早退
平日普通残
平日深夜残
平日休憩
休日普通残
休日深夜残
休日休憩
などのように漏れなく列を設けてそれぞれしっかり計算し,合計するような作り方に勤務表全体を見直す事をお薦めします。
再度の回答ありがとうございました。
ご指摘は分かっていたつもりでしたが実際は理解出来ておりませんでした。
実際早出で入力したらおかしいことに気付き、締めてしまった後でしたので再度質問した次第です。
ネットで調べた情報を元に付け足したりしていた為、ごちゃごちゃになり更に分からなくなっている現状でもありますので、これを機に関数をちゃんと勉強しようと思います。
ありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- その他(ビジネス・キャリア) 勤務時間の割増について教えてください。 現在日勤と夜勤で勤務しています。 日勤は9:00から20:0 5 2022/05/08 22:40
- 転職 転職活動中で2社内定があり迷っています。皆様ならAとBどちらの方が良いと思いますか?理由もお願いしま 1 2023/02/06 12:20
- 会社・職場 ブラック企業か判断お願いします。 半休使い週6勤務。 休憩時間は、1時間半。しかし、まともに取れるの 5 2023/03/08 20:20
- 飲食業・宿泊業・レジャー 休憩・残業時間の自動算出 1 2023/01/07 22:58
- 労働相談 食品工場でシフト制3交代勤務をしているものです。 休憩時間ほぼなし。 月の残業時間40~60時間。 1 2022/05/24 10:48
- その他(就職・転職・働き方) 内定を3社貰って悩んでます 8 2022/08/10 04:38
- Excel(エクセル) エクセルの時間の計算、何時以下の場合の足し引きに苦戦しています。 2 2022/11/16 16:39
- Excel(エクセル) TEXT関数(負の値)を集計のため数値に変換したい 5 2022/05/15 23:04
- 会社・職場 仕事上での不満、相談です。 こんにちは、ご覧頂きありがとうございます。 私は現在接客業(旅館業務)に 4 2023/04/20 12:01
- 転職 現在郵便局の契約社員で配達や集荷で3年程勤めています。正社員になりたく転職活動をしております。内定が 5 2023/02/12 01:07
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
残業中に音楽を聴くのはいけな...
-
自己成長に繋がらない仕事について
-
ノートの大きさ A3とB5
-
時間の無駄づかい。
-
仕事を早く覚えるのにおいて重...
-
新卒 転職 人間関係 新卒1年目...
-
地方公務員で昇進できない人は?
-
「各位」は目上の方には失礼?
-
女性の営業さんが好きになりま...
-
自分にだけ態度が違う職場の先...
-
仕事だから仕方がない?彼氏と...
-
部下に尊敬してますと言われた...
-
職場で配られるお菓子に迷惑し...
-
将来について悩んでいます
-
山口組にはいるには?
-
年下の女性部下を持つ40代の男...
-
「~してもらえるとうれしいで...
-
26歳女が人生を巻き返す方法
-
就活、学業、サークル、どれを...
-
スーパー売価変更ミスを無くす方法
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
残業中に音楽を聴くのはいけな...
-
家をでる時間と帰宅時間また通...
-
エクセル 数式を教えてください!
-
「各位」は目上の方には失礼?
-
「~してもらえるとうれしいで...
-
女性の営業さんが好きになりま...
-
地方公務員で昇進できない人は?
-
他部署の仕事を押し付けられた...
-
取引先の担当とその上司とEメー...
-
他人のやる事なす事にケチつけ...
-
部下より基本給が安くモチベー...
-
話をしている最中に部下に電話...
-
日本人は褒めない人が多いよう...
-
就活における親友への嫉妬、劣...
-
公務員か、民間か迷っている日...
-
年下の女性部下を持つ40代の男...
-
職場で配られるお菓子に迷惑し...
-
工場勤務を2日目ですが辞めた...
-
新しい職場に入ったのですが 苗...
-
承認者不在時の押印の仕方について
おすすめ情報