エクセルド素人です。質問の意味も的確に伝えたれるかわかりませんが、教えて下さい。
アルバイトの方の出勤簿で、勤務時間のうち早朝・通常・遅出・深夜の時間数を計算できる公式が知りたいです。
早朝(5:00~8:00) 通常(8:00~18:00) 遅出(18:00~22:00) 深夜(22:00~翌日5:00)に設定したいです。
例(1) 7:00~9:30の場合は、早朝[1時間] 通常〔1時間半]
例(2) 16:00~20:00の場合は、通常[2時間] 遅出[2時間]
例(3) 19:00~25:30の場合は、遅出[3時間] 深夜[3時間半]
例(4) 21:00~翌日9:00の場合は、遅出[1時間] 深夜[7時間] 早朝[3時間] 通常[1時間]
必ず同じ時間に出勤するということもなく、日をまたいでしまうこともあるので、とてもややこしく、いろんな式を試してみましたが上手く行きません。ド素人の質問でも、私のやりたいことがわかって下さる方、どうかお助けお願いします。
A(日付) B(曜日) C(出勤時間) D(終了時間) E(早朝) F(通常) G(遅出) H(深夜)
1 6日 水 7:00 9:30 1:00 1:30
2 7日 木 16:00 20:00 2:00 2:00
3 8日 金 19:00 25:30 3:00 3:30
4 9日 土 21:00 9:00 3:00 1:00 1:00 7:00
C(出勤時間)とD(終了時間)を入力すると、E(早朝)・F(通常)・G(遅出)・H(深夜)を自動で計算することは可能でしょうか?
A 回答 (5件)
- 最新から表示
- 回答順に表示
No.5
- 回答日時:
追記。
8日(金)の例のように、深夜00:00を跨いでの勤務があるので、こういう場合は工夫が必要です。
・方法1:勤務時間を2つに別ける。
勤務時間を19:00~24:00、00:00~01:30(25:00)の2つの範囲に分割して計算します。
すると、時間帯区分は、0:00~24:00の区間だけ考えれば良いので
早朝(5:00~8:00)
通常(8:00~18:00)
遅出(18:00~22:00)
深夜1(22:00~24:00)
深夜2(0:00~5:00)
の5つだけ考えれば良い事になります。
早朝=早朝(5:00~8:00)と19:00~24:00が重複する時間数+早朝(5:00~8:00)と00:00~01:30が重複する時間数
通常=通常(8:00~18:00)と19:00~24:00が重複する時間数+通常(8:00~18:00)と00:00~01:30が重複する時間数
遅出=遅出(18:00~22:00)と19:00~24:00が重複する時間数+遅出(18:00~22:00)と00:00~01:30が重複する時間数
深夜=深夜1(22:00~24:00)と19:00~24:00が重複する時間数+深夜1(22:00~24:00)と00:00~01:30が重複する時間数+深夜2(0:00~5:00)と19:00~24:00が重複する時間数+深夜2(0:00~5:00)と00:00~01:30が重複する時間数
・方法2:時間帯を2種類用意する。
勤務終了時間が24:00を超えるので、時間帯区分を「24時を超えてないもの」と「24時を超えたもの」と2種類用意します。
すると、時間帯区分は
深夜1(0:00~5:00)
早朝1(5:00~8:00)
通常1(8:00~18:00)
遅出1(18:00~22:00)
深夜2(22:00~29:00)
早朝2(29:00~36:00)
通常2(36:00~42:00)
遅出2(42:00~46:00)
深夜3(46:00~48:00)
の9種類になります。
早朝=早朝1(5:00~8:00)と19:00~25:30が重複する時間数+早朝2(29:00~36:00)と19:00~25:30が重複する時間数
通常=通常1(8:00~18:00)と19:00~25:30が重複する時間数+通常2(36:00~42:00)と19:00~25:30が重複する時間数
遅出=遅出1(18:00~22:00)と19:00~25:30が重複する時間数+遅出2(42:00~46:00)と19:00~25:30が重複する時間数
深夜=深夜1(0:00~5:00)と19:00~25:30が重複する時間数+深夜2(22:00~29:00)と19:00~25:30が重複する時間数+深夜3(46:00~48:00)と19:00~25:30が重複する時間数
---
方法1だと退勤時刻が24:00より大きい場合、勤務時間を2つに分割する処理が入るので、式が複雑になります。
方法2だと、退勤時刻が「25:30」とかになっていても、そのまま計算できるので、式が簡単になります。
なので、方法2の方が楽に書ける筈です。
>IF関数の入力の仕方も教えていただけると嬉しいのですが、無理でしょうか?!
基本は
=IF(OR(AとDの関係式,BとCの関係式),0,IF(BとDの関係式,B,D)-IF(AとCの関係式,A,C))+IF(OR(AとD’の関係式,BとC’の関係式),0,IF(BとD’の関係式,B,D’)-IF(AとC’の関係式,A,C’))
です。
A、Bには「出勤時刻、退勤時刻」が入ったセルを書きます。
C、Dには、深夜なら「0:00、5:00」など、1つ目の時間帯区分を書きます。
C'、D'には、深夜なら「22:00、29:00」など、2つ目の時間帯区分を書きます(深夜は3つ目もあるので注意)
「関係式」は、前の回答にある通りの「大小比較をする式」を書きます。
chie65535様
昨日からご丁寧な回答ありがとうございます。24:00をまたぐと、とてもややこしくなるので、0:00で一旦区切って考えれば良いというヒントは、大変役に立ちました。基本の式も教えて頂き、ありがとうございました。参考にさせてもらいながら、今から格闘してみたいと思います。
No.2
- 回答日時:
A~B間、C~D間の2つの「区間」があった時、その2つの区間の「重なり具合」は、添付図のようになります。
この時、必ず「A<B」「C<D」とします。
AとC、AとD、BとC、BとDのそれぞれの関係と、重なり具合を色分けすると「どの関係を調べれば、どう重なっているかが判る」ようになります。
A~B間を「出勤から退勤まで」、C~D間を「早朝、通常、遅出、深夜1、深夜2」とした時、AとC、AとD、BとC、BとDのそれぞれの関係を調べると「それぞれの重なり具合」が判ります。
なお「深夜」が0時(24時)を跨ぐと面倒なので、
深夜1=22:00~24:00
深夜2=00:00~05:00
として別々に考え、最後に「足し算」します。
添付図で「より後」と「等しい」が同じ色になっているので、その部分は「前でない」と考えます。
「より前」と「等しい」が同じ色になっている部分も「後でない」と考えます。
重なりが「AからD」になっている部分の「重なってる時間数」は「D-A」の引き算で計算できます。
その他の重なりも、同様の引き算で計算できます。
こう見ると「上のなし」「AからDまで」「AからBまで」「CからDまで」「CからBまで」「下のなし」の6種類あると判ります。
6種類ですが「なし」「から」「まで」を別々に考えれば
「なし」または「なし以外」
「Aから」または「Cから」
「Dまで」または「Bまで」
の3つを判断すれば良いと判ります。
『「なし」または「なし以外」』は「AとDの関係」と「BとCの関係」で判断できます。
『「Aから」または「Cから」』は「AとCの関係」で判断できます。
『「Dまで」または「Bまで」』は「BとDの関係」で判断できます。
あとは「上記の通りにIF関数を書くだけ」です。
chie65535様 ご丁寧に説明して下さったり、画像を添付して頂き、ありがとうございます。 今は携帯からしか見られず、画像が見られなかったので、明日会社のパソコンで確認させてもらいます。 IF関数の入力の仕方も教えていただけると嬉しいのですが、無理でしょうか?!
No.1
- 回答日時:
一つのセルで色々な条件を付けて計算しようとすると、長い数式になり修正が非常に大変になります。
なるべく平易な分かりやすい数式となるように、各時間帯別に、勤務開始時刻、勤務終了時刻、休憩開始時刻、休憩終了時刻を区切り、通常勤務時間、通常残業時間、深夜残業時間、合計勤務時間を計算しています。
https://sites.google.com/site/kinnmujikannnokeis …
aokii様 早々の回答ありがとうございます。パソコンに不慣れなもので、アドレスにアクセスさせて頂きましたが、ファイルを開くことが出来ず、確認することが出来ませんでした。申し訳ありません。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) 指定した値以上の中で最小値を出したい 7 2022/10/24 21:12
- その他(ビジネス・キャリア) 勤務時間の割増について教えてください。 現在日勤と夜勤で勤務しています。 日勤は9:00から20:0 5 2022/05/08 22:40
- 労働相談 夜勤の1週間シフトについて 月曜日の夜8時から朝の7時まで勤務します。 1週間のシフトが土曜日の朝で 3 2023/07/05 01:30
- その他(メンタルヘルス) 朝起きられない。 4 2022/05/10 09:15
- 睡眠障害・不眠症・過眠症 睡眠に詳しい方へ質問です 2 2022/12/17 10:50
- 求人情報・採用情報 介護の求人についてお伺いしたいです。 現在フリーターでアルバイトを探しており、グループホームの求人を 1 2023/03/20 23:15
- 人事・法務・広報 会社の行事などで休日労働させる時、振替休日が休日労働した日の前週or翌週以降、月をまたいだ場合の手当 3 2023/05/21 23:37
- 退職・失業・リストラ 早朝勤務について 5 2023/03/12 13:16
- 人事・法務・広報 みなし残業と手当の関係 1 2023/04/12 09:23
- 会社・職場 職場で今日、いろいろ言われました。 私は子どもが小さいため、パートで週に3日、九時半~出勤しています 11 2023/05/01 15:24
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
至急!!体調不良でバイト休むべきか
-
1分単位のタイムカード計算でエ...
-
7時間55分勤務で1ヶ月20日出勤...
-
建設業・夜勤の深夜労働金額の...
-
月途中で通勤手当が変更となる...
-
何進法なのかわからない
-
有給休暇は出勤日数となるか
-
週4日出勤の方の残業時間の出し...
-
賃金台帳の労働日数とは
-
ラブホから居心地良すぎて帰れ...
-
エクセルの時間の計算、何時以...
-
大きい企業には休日出勤(日曜)...
-
休日出勤を勤務日数に数えるか?
-
タイムカード。エクセルで一日...
-
日をまたぐ関数を教えて下さい
-
Excel関数で日またぎの勤務時間...
-
無断で休日出勤した者への給与
-
IF関数について、14時以降の...
-
「ありがとうございます。 また...
-
法定休日に出勤し振休をとった...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
大きい企業には休日出勤(日曜)...
-
1分単位のタイムカード計算でエ...
-
至急!!体調不良でバイト休むべきか
-
エクセル不変則な休憩時間の実...
-
建設業・夜勤の深夜労働金額の...
-
月途中で通勤手当が変更となる...
-
7時間55分勤務で1ヶ月20日出勤...
-
人工出し
-
会社って、無断出勤するとどう...
-
有給休暇は出勤日数となるか
-
給与明細と実際の出勤日数が違う
-
バイトでタイムカードを帰りの...
-
休日出勤を勤務日数に数えるか?
-
土日の有給休暇の取得はできる...
-
賃金台帳の労働日数とは
-
出勤前後とはどういう意味です...
-
何進法なのかわからない
-
交通費(定期代)の日割計算
-
残業時間の計算をするにはどの...
-
タイムカード。エクセルで一日...
おすすめ情報