

エクセルで添付のような勤務表を作成しています。
労働時間(表中・小計)に応じた休憩時間は自動算出出来るようになったのですが、まだ課題がありまして…
それは、お昼休憩を挟んだ時の休憩時間についてです。
うちでは、お昼を挟むと、勤務時間に関係なく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で質問しましょう!
このQ&Aを見た人はこんなQ&Aも見ています
-
複数の休憩時間がある場合の休憩時間の算出方法
Excel(エクセル)
-
Excelの時間計算で経過時間から特定の時間を引く方法
Excel(エクセル)
-
Excel タイムカードで、昼休み時間を除く計算
Excel(エクセル)
-
-
4
エクセルで休憩時間を引く時と、引かない時の数式
Excel(エクセル)
-
5
IF関数について、14時以降のみに1時間の休憩を計算したい。
その他(ビジネス・キャリア)
-
6
エクセルで複数の勤務時間ごとの休憩時間の計算がしたい
Excel(エクセル)
-
7
エクセルで表示形式の時刻の「0:00」を表示しないようにするには?
Excel(エクセル)
-
8
エクセルで勤怠管理表で休憩時間を計算させるには
Excel(エクセル)
-
9
excel関数 変動する休憩時間等を計算する
ソフトウェア
-
10
エクセル不変則な休憩時間の実労働時間計算
Excel(エクセル)
-
11
エクセルで休憩時間を計算したいのですが 条件 勤務時間6時間以上8時間未満は休憩45分 8時間以上は
Excel(エクセル)
-
12
休憩時間が数パターンある場合の休憩時間の計算
Excel(エクセル)
-
13
勤務表の中抜け集計の関数を教えてほしいです。
Excel(エクセル)
-
14
エクセルで休憩時間を引く時と、引かない時の数式
Excel(エクセル)
-
15
IF関数で、時間を条件にしたい場合の式について
Access(アクセス)
-
16
[IF文] 「何時〜何時」で該当する時間を表示したい お疲れ様です。お知恵をお借りできればと思います
Excel(エクセル)
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
LibreOffice Clalc(またはエク...
-
【マクロ】excelファイルを開く...
-
【関数】3つのセルの中で最新...
-
【マクロ】数式を入力したい。...
-
Office2021のエクセルで米国株...
-
エクセルのリストについて
-
【マクロ】【相談】Excelブック...
-
【マクロ】元データと同じお客...
-
【画像あり】オートフィルター...
-
エクセルの関数について
-
【マクロ】左のブックと右のブ...
-
他のシートの検索
-
【マクロ】実行時エラー '424':...
-
エクセルのVBAで集計をしたい
-
vba テキストボックスとリフト...
-
【マクロ】【配列】3つのシー...
-
【関数】=EXACT(a1,b1) a1とb1...
-
【マクロ画像あり】❶1つの条件...
-
エクセルの複雑なシフト表から...
-
【マクロ】変数に入れるコード...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【マクロ】元データと同じお客...
-
エクセルの関数について
-
【画像あり】オートフィルター...
-
エクセルのVBAで集計をしたい
-
エクセルのリストについて
-
【マクロ】数式を入力したい。...
-
【マクロ】【相談】Excelブック...
-
Office2021のエクセルで米国株...
-
【マクロ】実行時エラー '424':...
-
他のシートの検索
-
エクセルの複雑なシフト表から...
-
【マクロ】【配列】3つのシー...
-
vba テキストボックスとリフト...
-
【マクロ】左のブックと右のブ...
-
【マクロ】変数に入れるコード...
-
エクセルシートの見出しの文字...
-
【マクロ】別ファイルへマクロ...
-
【関数】同じ関数なのに、エラ...
-
Amazonでマイクロソフトオフィ...
-
ページが変なふうに切れる
おすすめ情報