![](http://oshiete.xgoo.jp/images/v2/pc/qa/question_title.png?8acaa2e)
エクセルで、土日祝日と休憩時間を除いて、開始日時、作業時間から
終了日時をだしたいのですがどのようにしたらよいかわかりません。
例えば
開始日時:2021/04/09 10:00
作業時間:1350分
稼働時間は、土日祝日を除く、8:45~17:00
ただし、1日のうち以下3回の休憩時間は除く
①10:15~10:30
②12:00~12:45
③15:15~15:30
結果は、以下になると思います。
⇒終了日時:2021/04/14 11:45
この終了日時をExcelで計算したいです。
Excel2019使用です。
どのようにしたらよいでしょうか・・。
作業用セルを使って複数回にわけて結果をだす数式でもかまいません。
プログラムを記述しないとできませんでしょうか・・。
よろしくお願いいたします。
![「エクセルで終了日時を計算したいです。土日」の質問画像](http://oshiete.xgoo.jp/_/bucket/oshietegoo/images/media/b/543088046_6072a60e3773f/M.png)
A 回答 (5件)
- 最新から表示
- 回答順に表示
No.5
- 回答日時:
No.3、4です。
前回回答に誤りがあったため、修正して再投稿いたします。
考え方は、変えておりませんが、休憩時間表と数式を修正しました。
休憩時間表と組み合わせてベタに計算する方法です。
開始時間に稼働時間外の時刻を入力した場合、結果は検証対象外です。
(1)一日の実作業時間を7時間(420分)として、開始日時、作業時間から終了日時を計算する
(2)「開始日時」が開始日の8:45より後の時間である場合、終了時刻は後ろへずれることになる
(3)終了時刻が後ろへずれるということは、見かけ上は作業時間が余計にかかることと同じ
(4)開始日の開始時刻が8:45より後の時刻の場合、終了日時を計算するのに、開始日の8:45に開始したと考えた場合と比べて、どれだけ実作業時間が長くなったと仮定すれば、8:45スタートと開始時間との差を調整できるのかを計算し、実際の実作業時間にその時間を加えて「仮想所要時間」を算出
(5)添付画像のように、E5~G7に休憩時間を8:45からの経過時間(分)で表した一覧表を作成。G列にはE列とF列の間の値になると「実労働時間」の時計がその時間から進まないという値(例.10:15~10:30の間は実労働時間90分から時計が進まない)を入力済み。
※前回回答はこの表に誤りがあったため修正。
(6)H5から下方向へ祝日を入力し「祝日」という範囲名を付与。
(とりあえず、4月、5月の祝日のみ入力してあります。実際の場面ではすべて入力しないと正しい結果になりません)
(7)E2に以下の数式を記述し「仮想所要時間」を算出
=ROUND(B2+$F$2-IFERROR(IF($F$2<=VLOOKUP($F$2,$E$5:$G$7,2,1),$F$2-VLOOKUP($F$2,$E$5:$G$7,3,1),VLOOKUP($F$2,$E$5:$G$7,2,1)-VLOOKUP($F$2,$E$5:$G$7,3,1)),0),0)
(8)F2に開始時刻と始業時刻との差を算出
=(TEXT(MOD(A2,1),"h:mm")-"8:45")*60*24
(9)G2にE2を420で割った余りを算出
=MOD(E2,420)
(10)C2に以下の数式を記述し「終了日時」を算出
=WORKDAY(INT(A2),IF(B2=0,0,ROUNDUP(E2/420,0)-1),祝日)+IF(B2=0,MOD(A2,1),IF(G2=0,"17:00","8:45")+IFERROR(IF($G$2=VLOOKUP($G$2,$G$5:$G$7,1,1),INDEX($E$5:$E$7,MATCH($G$2,$G$5:$G$7,1)),$G$2+INDEX($F$5:$F$7,MATCH($G$2,$G$5:$G$7,1))-VLOOKUP($G$2,$G$5:$G$7,1,1)),$G$2)/60/24)
整理できていないため、力業の数式ですが、ご容赦ください。
※全ての場合を検証している訳ではありませんので、正しくない結果を返す場合があるかもしれません。その場合はご容赦ください。ご指摘いただければ可能な限り修正します。
前回回答では、私の勘違いもあり、正しい検証ができませんでしたので、fujillinさんの回答をもう一度検証して見ました。
他の条件を変えずに開始日時を2021/4/9 10:35としてみると、終了日時が2021/4/14 12:10と表示されます。やはり、休憩時間中に作業終了することになってしまいました。
さらに、同様に8:55と入力してみると2021/4/14 10:35となります。
これは5分の誤差があるように思います。正しくは10:40ではないでしょうか?
![「エクセルで終了日時を計算したいです。土日」の回答画像5](http://oshiete.xgoo.jp/_/bucket/oshietegoo/images/media/c/142736_607841f4595c7/M.png)
No.4
- 回答日時:
No.3です。
申し訳ありません。
fujillinさんに大変な失礼をしてしまいました。
全くの私の勘違いでした。
ご質問者の指定は休憩時間が10:00~10:15ではなく、10:15~10:30でした。
従って、No.3の回答は意味をなしません。
無かったことにさせてください。誠に申し訳ありませんでした。
No.3
- 回答日時:
既に、fujillinさんによる回答が示されています。
大変失礼とは思いますが、当方で検証してみたところ、若干おかしな結果を返す場合があることが判りました。
他の条件は変えずに、現実では絶対に起こりえないと思う、終業5分前である、2021/4/9 16:55に業務を開始することにしてみました。
すると、終了日時が2021/4/15 10:10と表示されました。
そもそも、10:00から10:15の間は休憩時間ですから、休憩時間中に業務が終了するというのは矛盾しています。
2021/4/15 10:25と表示されるのが正しいと思います。
一日の労働時間を7時間(420分)として、作業時間から終了日時を計算するのですが、「開始日時」が開始日の8:45より後の時間であるということは、終了時刻も後ろへずれることになります。、
終了時刻が後ろへずれるということは、見かけ上は作業時間が余計にかかることと同じです。そこで、開始日の開始時刻が8:45より後の時刻の場合、終了日時を計算するのに、開始日の8:45に開始したと考えた場合よりどれだけ作業時間が長くなったと仮定すればよいのかを計算し、実際の作業時間にその時間を加えて「仮想所要時間」を算出してから計算することにしてみました。
添付画像をご覧ください。
E5~G7は休憩時間を8:45からの経過時間(分)で表した一覧表になっています。G列にはE列とF列の間の値になると「実労働時間」の時計がその時間から進まないという値(例.10:00~10:15の間は実労働時間75分から時計が進まない)を入れてあります。
また、H5から下方向へ祝日を入力し「祝日」という範囲名をつけてあります。(とりあえず、4月、5月の祝日のみ入力してあります。実際の場面ですべて入力しないと正しい結果になりません)
前述のとおり、E2に以下の数式を記述し「仮想所要時間」を算出します。
=B2+(MOD(A2,1)-"8:45")*60*24-IFERROR(IF((MOD(A2,1)-"8:45")*60*24<=VLOOKUP((MOD(A2,1)-"8:45")*60*24,$E$5:$G$7,2,1),(MOD(A2,1)-"8:45")*60*24-VLOOKUP((MOD(A2,1)-"8:45")*60*24,$E$5:$G$7,3,1),VLOOKUP((MOD(A2,1)-"8:45")*60*24,$E$5:$G$7,2,1)-VLOOKUP((MOD(A2,1)-"8:45")*60*24,$E$5:$G$7,3,1)),0)
仮想所要時間から、終了日時を算出します。C2に
=WORKDAY(INT(A2),INT(E2/420),祝日)+"8:45"+(MOD(E2,420)+IFERROR(VLOOKUP(MOD(E2,420),$E$5:$G$7,2,1)-VLOOKUP(MOD(E2,420),$E$5:$G$7,3,1),0))/60/24
という数式を記述します。
これで、ご質問者のご希望の結果が得られるはずです。
ただし、fujillinさんの回答と同様に全ての場合を検証している訳ではありませんので、正しくない結果を返す場合があるかもしれません。
その場合はご容赦ください。ご指摘いただければ可能な限り修正します。
![「エクセルで終了日時を計算したいです。土日」の回答画像3](http://oshiete.xgoo.jp/_/bucket/oshietegoo/images/media/2/142736_60766bff38df7/M.png)
No.1
- 回答日時:
こんばんは
回答がつかないようなので・・・
休憩が必ずしも規則的とは言えないので、実時間を求めるのに順に計算しなければならないのが面倒ですね。
もう少し、スマートな方法がありそうな気もしますけれど、休憩時間表と併せてベタに計算する方法の例をご参考までに挙げておきます。
添付図では、A5:C11を仮のデータ表としています。
(A5:B11は休憩時間表で固定値を入力、C5:C11が計算用作業セルです)
提示の都合で、A5:C11としましたが、別シートでもどこでも構いません。
1)C5セルに、
=MOD(B2/60/24,7/24)
を入力。
2)C6セルに、
=(MOD(A$2,1)<B6)*(MOD(A$2,1)+C5>A6)*(MIN(B6,MOD(A$2,1)+C5)-MAX(A6,MOD(A$2,1)))+C5
を入力し、C11までフィルコピー。
3)C2セルには、
=WORKDAY(A2,INT(B2/60/7)+((MOD(A$2,1)+C11)>17/24))+MOD(A$2,1)+C11-((MOD(A$2,1)+C11)>17/24)*8.25/24
の式を入力してあります。
※ 日数計算には WORKDAY関数を利用していますが、祝日データは添付図には含まれていません。
通常の WORKDAY関数を利用する際と同様に祝日を設定しておくことで、祝日も除いて計算ができるようになります。
https://support.microsoft.com/ja-jp/office/workd …
![「エクセルで終了日時を計算したいです。土日」の回答画像1](http://oshiete.xgoo.jp/_/bucket/oshietegoo/images/media/f/587726_60742055e372c/M.png)
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) セルに特定の色が出た時だけ、式を発動させたい 4 2022/06/17 10:32
- Excel(エクセル) エクセルで休憩時間を引いての作業計画予定表の作成の仕方 2 2023/07/24 14:11
- Excel(エクセル) エクセルで勤務時間の計算をしています。 下記図でstartは勤務開始時間、endは勤務終了時間です。 5 2022/06/07 13:51
- Excel(エクセル) 指定した値以上の中で最小値を出したい 7 2022/10/24 21:12
- その他(Microsoft Office) エクセル 条件付き書式 日をまたぐ塗りつぶし 1 2023/01/13 18:00
- Excel(エクセル) エクセル2019の関数を教えてください。 8 2022/12/16 12:45
- 派遣社員・契約社員 残業時間について教えてください 5 2022/12/17 10:58
- 転職 転職活動中で2社内定があり迷っています。皆様ならAとBどちらの方が良いと思いますか?理由もお願いしま 1 2023/02/06 12:20
- Excel(エクセル) エクセル 条件に合う日付に入力された時間数の合計したい 4 2022/06/17 22:18
- Excel(エクセル) エクセル「社員の重なっている仕事時間の算出方法について」教えてください。 6 2023/02/06 00:10
このQ&Aを見た人はこんなQ&Aも見ています
-
「環境が人を育てる」って本当?環境によって人格や生き方は本当に変わるのか
環境が人生に与える影響は実際どれほどのものなのか、専門家の田宮由美さんに伺った。
-
Excelの時間計算で経過時間から特定の時間を引く方法
Excel(エクセル)
-
エクセルで勤怠表 土日祝と平日と分けてそれぞれの合計を出したい
Excel(エクセル)
-
複数の休憩時間がある場合の休憩時間の算出方法
Excel(エクセル)
-
-
4
エクセルで勤務時間を出したい。 土日、祝日と18時以降、朝9時までを取り除きたい
Excel(エクセル)
-
5
エクセル 平日と土日祝の時間計算表示
その他(OS)
-
6
Excel タイムカードで、昼休み時間を除く計算
Excel(エクセル)
-
7
エクセルで、時間の計算をした場合、マイナス表示を0:00にする方法を教えてください。
Excel(エクセル)
-
8
エクセルで休憩時間を除く
その他(Microsoft Office)
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルのツールバーから数値...
-
祝日と土曜、日曜の合計をカウ...
-
【マクロ】2回実行したら、エ...
-
特定の文字列を含む、住所を抽...
-
EXCELの散布図で日付が1900年に...
-
マイクロソフトのPADを使ってい...
-
Excel分数の表示について
-
Excelで表を作ったところに文字...
-
マクロエクセルのブロック解除
-
文字2桁、3桁交じりの文字列...
-
絶対参照
-
Excelについての質問です。 B2...
-
在庫管理表に使うエクセルの関...
-
【マクロ】VLOOKUPにて参照元に...
-
【EXCEL】画像の黄色部分の抽出...
-
DATE関数で現在の年齢を出した...
-
ユーザー定義関数をアドイン登...
-
エクセルでの作業計算方法について
-
行数が不規則な一週間ごとの合...
-
Excelピボットテーブルの1行目
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
半角カタカナをヘボン式ローマ...
-
(マクロ)vlookupの元データを同...
-
エクセルで上位バイトのセルと...
-
exselの質問です
-
Excel 大小比較演算子による「...
-
Excel VBについての質問です。
-
エクセルの問題です。絶対値の...
-
非表示列の再表示に失敗
-
職場の人から聞かれており、こ...
-
Excel関数-文字列で自動作成さ...
-
Excelデータをコピペして、ペー...
-
ユーザー定義関数をアドイン登...
-
【マクロ】for next構文について
-
エクセルの日付を編集する
-
【マクロ】VLOOKUPにて参照元に...
-
exselで最小数で並び替える関数
-
libre 表計算ソフトの計算がう...
-
エクセルで表
-
エクセルの表で1年間の曜日を...
-
西暦和暦
おすすめ情報