エクセルで添付のような勤務表を作成しています。
労働時間(表中・小計)に応じた休憩時間は自動算出出来るようになったのですが、まだ課題がありまして…
それは、お昼休憩を挟んだ時の休憩時間についてです。
うちでは、お昼を挟むと、勤務時間に関係なく1時間の休憩になります。(お昼休憩)
例えば、9時~18時だと、小計9時間・休憩1時間(8時間以上なので)・実労働(表中・合計)時間8時間となるので大丈夫なのですが。
9時~15時の勤務だと、小計6時間・休憩30分・実働5時間30分になってしまいます。
これを、小計6時間・休憩1時間・実働5時間になるように計算させたいのです。
表中の各計算式は以下です。
C8→勤務開始を入力
D8→勤務終了を入力
D8の小計→=IF(D8<C8,1+D8-C8,D8-C8)
F8の休憩→=IF(E8<"4:00"*1,0,IF(E8<"6:00"*1,10,IF(E8<"7:00"*1,30,IF(E8<"8:00"*1,40,60))))/24/60
G8の合計→=E8-F8
です。
アドバイスよろしくお願いします。
No.1ベストアンサー
- 回答日時:
であれば、F列のIF関数の最初に次の条件を追加すればいけるのでは?
「業務開始時間が12時より前かつ、勤務終了時間が13時より後ろだったら、休憩時間は1.0hとする」
=IF(AND(C8<"12:00",D8>"13:00"),1,IF(E8<"4:00"*1,0,IF(E8<"6:00"*1,10,IF(E8<"7:00"*1,30,IF(E8<"8:00"*1,40,60))))/24/60)
この回答への補足
先ずはご回答ありがとうございます。
こ、これです!この条件をどう入力すれば良いのか、また「○時間」以上の「時:分」と「○時○分」の「時:分」が両立してくれるものか悩んでしまって行き詰っておりました。
私の探していたイメージぴったりの式で、どう条件設定をすれば良いか勉強になりました。
イメージぴったりという事で、この式でやってみてOKだったら質問を締め切ろうかとも思ったのですが、他の角度からのアプローチもあり、今後の参考・勉強のためにもご回答くださった皆様の式を試したいと思っております。
お礼が遅くなってしまいますが、今エクセルソフトが手元にないため、お礼とベストアンサーについては色々な検証が終わり、疑問が解決(課題クリア)しましたら改めて書かせて頂きますのでよろしくお願いします。
エクセルの使用環境がありましたので検証してまいりました。
その結果…どうにもうまく行きませんでした(涙)
丸々コピーしたので打ちミスはないと思います。また、一応抜け?などが無いかも見たのですが分からず…
書式設定の変更などをすれば解決するでしょか?
具体的には、9時~15時と打っても、今までと同じく30分間の休憩のみとなってしまいます。
補足を使用してしまったのでお礼欄への記入で失礼いたします。
他に、方法やアドバイス等ありましたら、引き続きよろしくお願い致します。
siremonoさま、ヒントとなる回答、ありがとうございました!
No.3
- 回答日時:
小計の計算の式から判断すると深夜12時を過ぎた時間は0:30のように入力するようになっていますが休憩時間の計算式が複雑になりますので24:30のように入力するとします。
そこでF8セルへは次の式を入力します。=IF(MIN("13:00",D8)>MAX("12:00",C8),MIN("13:00",D8)-MAX("12:00",C8),0)+IF(D8>"13:00"*1,IF(D8-MAX("13:00",C8)<"4:00"*1,0,IF(D8-MAX("13:00",C8)<"6:00"*1,"0:10",IF(D8-MAX("13:00",C8)<"7:00"*1,"0:30",IF(D8-MAX("13:00",C8)<"8:00"*1,"0:40","1:00")))))
この式では午前中からの13時過ぎまで働いた場合には12時から13時まで休憩の時間を取っていますので、13時以降の時間について働いた時間を計算して休憩時間を計算するようにしています。なお、12時30分から勤務が始まっている場合には13時までは休憩時間にしています。
例えば12:30に勤務が始まって20:00に終了した場合には13時以降の勤務が7時間となることから40分の休憩と昼の時間の30分がプラスされて合計の休憩時間は1時間10分となります。
この回答への補足
先ずはご回答ありがとうございます。
えぇと、せっかく素晴らしい回答くださったのに…こちらの説明不足でちょっと実用的では無くなってしまいました(汗)
実は、この勤務表、夜勤の計算にも使用しております。
(夜勤は日勤でいうお昼休憩という概念はなく、一律1時間です。全日8時間以上なため。)
また以前は、翌9時などを33時と打っており、この脳内変換作業がミスの元になったので、入力方法を現在の形にしたという経緯があります。(0時ちょっと過ぎくらいが多いなら24時で対応出来たのですが、大抵は朝の時間帯が勤務終了時間なので…。タイムカードには9時と印字されるので、うっかりそのまま打つ…とか。)
式の解説を読むと「おぉ!」と声をあげるくらい取り入れたいと思った式なのですが、本当に申し訳ありません。
私事ですが、今エクセルソフトが手元にないため、お礼とベストアンサーについては検証が終わり、疑問が解決(課題クリア)しましたら改めて書かせて頂きますのでよろしくお願いします。
No.2
- 回答日時:
取り敢えずJ列とK列を使ってテーブルを作り、それをVLOOKUP関数で取り出すという方法を・・・
E8= IF(D8="","",(D8+IF(D8-C8<0,1,0))-C8) ←この式を下へコピー
F8= IF(E8="","",VLOOKUP(ROUNDUP(E8,3),$J$7:$K$11,2,1))←この式を下へコピー
G8= IF(E8="","",E8-F8)←この式を下へコピー
一応エラー処理してます
サンプル画像を添付しておきます <(_ _)>
この回答への補足
先ずはご回答ありがとうございます。
サンプル画像まで付けて頂きありがとうございます。
実は恥ずかしながらエラー表示が出ているのは知っていたのですが、一体どれが原因でエラーが出たかまでは未検証・未確認で、きちんと理解しておりませんでした。
今回、エラー処理して頂いてるとのことで、見比べて(…これで分かれば一発で理解してそうですが;;)前回のエラーの原因をきちんと確認・理解したいと思います。
私事ですが、今エクセルソフトが手元にないため、お礼とベストアンサーについては検証が終わり、疑問が解決(課題クリア)しましたら改めて書かせて頂きますのでよろしくお願いします。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- アルバイト・パート バイトの休憩時間に関してです。 大学生で友達とふたりでバイトを始めました。 飲食店でオープニングスタ 3 2022/12/04 22:45
- その他(ビジネススキル・経営ノウハウ) 今まで午後勤務しかやったことありません。 8時から17時で9時間休憩入れて8時間勤務に変わります。お 1 2023/02/07 21:50
- Excel(エクセル) エクセル2019の関数を教えてください。 8 2022/12/16 12:45
- Excel(エクセル) エクセルで勤務時間の計算をしています。 下記図でstartは勤務開始時間、endは勤務終了時間です。 5 2022/06/07 13:51
- 憲法・法令通則 小さな会社経営者です。 勤務時間9時半から18時半 休憩1時間 9時間拘束の8時間労働の場合で 休憩 4 2023/06/30 21:45
- Excel(エクセル) エクセルで休憩時間を引く時と、引かない時の数式 3 2022/11/05 11:48
- 飲食業・宿泊業・レジャー 休憩・残業時間の自動算出 1 2023/01/07 22:58
- アルバイト・パート 計算の早い方、お願いします。 8時15分から17時までの勤務で、その間に、75分の休憩があれば、実働 3 2022/05/13 19:35
- Excel(エクセル) 月間シフト表から総勤務時間を計算する関数 4 2023/05/20 07:12
- アルバイト・パート パートで働いています。 時給が900円です。9時から5時まで勤務しているのですが、時給で計算すると8 7 2022/04/23 15:51
このQ&Aを見た人はこんなQ&Aも見ています
-
プロが教える店舗&オフィスのセキュリティ対策術
中・小規模の店舗やオフィスのセキュリティセキュリティ対策について、プロにどう対策すべきか 何を注意すべきかを教えていただきました!
-
複数の休憩時間がある場合の休憩時間の算出方法
Excel(エクセル)
-
エクセルで休憩時間を引く時と、引かない時の数式
Excel(エクセル)
-
Excelの時間計算で経過時間から特定の時間を引く方法
Excel(エクセル)
-
-
4
エクセルで複数の勤務時間ごとの休憩時間の計算がしたい
Excel(エクセル)
-
5
excel関数 変動する休憩時間等を計算する
ソフトウェア
-
6
エクセル不変則な休憩時間の実労働時間計算
Excel(エクセル)
-
7
エクセルで勤怠管理表で休憩時間を計算させるには
Excel(エクセル)
-
8
エクセルで休憩時間を計算したいのですが 条件 勤務時間6時間以上8時間未満は休憩45分 8時間以上は
Excel(エクセル)
-
9
Excel タイムカードで、昼休み時間を除く計算
Excel(エクセル)
-
10
IF関数で、時間を条件にしたい場合の式について
Access(アクセス)
-
11
8時間以上だったら1時間マイナスする関数
Excel(エクセル)
-
12
エクセルで表示形式の時刻の「0:00」を表示しないようにするには?
Excel(エクセル)
-
13
勤務表の中抜け集計の関数を教えてほしいです。
Excel(エクセル)
-
14
休憩時間の変動を含むExcelでの作業時間計算
Excel(エクセル)
-
15
エクセルで作業時間から休憩を差し引く数式
その他(ビジネス・キャリア)
-
16
IF関数について、14時以降のみに1時間の休憩を計算したい。
その他(ビジネス・キャリア)
-
17
エクセルで休憩時間を引く時と、引かない時の数式
Excel(エクセル)
-
18
休憩時間が数パターンある場合の休憩時間の計算
Excel(エクセル)
-
19
エクセルで時間の計算式を教えてください
Excel(エクセル)
-
20
【エクセル】休憩時間をマイナスし、実働時間の計算
Excel(エクセル)
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセル初心者です 関数の入れ...
-
【関数】先頭だけにある、半角...
-
エクセル 白黒印刷で白線を印刷...
-
Excelのチェックボックスの使い...
-
【関数】適切な文字数の数字を...
-
Excelのpivotについて質問です
-
Excel ピボットテーブルで日付...
-
LOOKUP関数を使えばいいのでし...
-
エクセル関数を教えてください
-
エクセルのセルに同じ大きさの...
-
UNIQUE関数が使えないバージョ...
-
excelの不要な行の削除ができな...
-
エクセルで「-0.0」と表示さ...
-
時間によってファイル名が変わ...
-
WPS OFFICEでの縦書きについて
-
エクセルの関数について教えて...
-
Aというブックの1というシート...
-
【マクロ】シート名を取得する...
-
VBA Private Sub Worksheet_Cha...
-
VBA、Excelのworkbook.open に...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報