エクセルで終了日時を計算したいです。土日祝日と休憩時間を除き、開始日時、作業時間から計算

エクセルで、土日祝日と休憩時間を除いて、開始日時、作業時間から
終了日時をだしたいのですがどのようにしたらよいかわかりません。

例えば
開始日時：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使用です。
どのようにしたらよいでしょうか・・。
作業用セルを使って複数回にわけて結果をだす数式でもかまいません。
プログラムを記述しないとできませんでしょうか・・。
よろしくお願いいたします。

No.5 回答者： goomania 回答日時： 2021/04/15 23:05

No.3、４です。

前回回答に誤りがあったため、修正して再投稿いたします。
考え方は、変えておりませんが、休憩時間表と数式を修正しました。
休憩時間表と組み合わせてベタに計算する方法です。
開始時間に稼働時間外の時刻を入力した場合、結果は検証対象外です。

(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ではないでしょうか？

この回答へのお礼

ご回答いただきありがとうございました。
ちょっとまだ理解できておらず・・。じっくり解読します。

お礼日時：2021/04/18 23:02

No.4 回答者： goomania 回答日時： 2021/04/14 23:13

No.3です。

申し訳ありません。
fujillinさんに大変な失礼をしてしまいました。
全くの私の勘違いでした。
ご質問者の指定は休憩時間が10:00～10:15ではなく、10:15～10:30でした。
従って、No.3の回答は意味をなしません。
無かったことにさせてください。誠に申し訳ありませんでした。

No.3 回答者： goomania 回答日時： 2021/04/14 13:14

既に、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さんの回答と同様に全ての場合を検証している訳ではありませんので、正しくない結果を返す場合があるかもしれません。
その場合はご容赦ください。ご指摘いただければ可能な限り修正します。

この回答へのお礼

ご回答いただきありがとうございました。

お礼日時：2021/04/18 23:01

No.2 回答者： fujillin 回答日時： 2021/04/12 23:15

No1です

No1の添付図の休憩時間データに誤りがあったようなので、訂正しておきます。

12:00～12:30となっているのは、12:00～12:45が正で、間違って入力してしまったようです。
（たまたま、例示のケースではその時間帯にかかっていないので、結果に影響は出ていませんけれど…）

失礼いたしました。

この回答へのお礼

こちらもありがとうございました。

お礼日時：2021/04/18 23:01

No.1 回答者： fujillin 回答日時： 2021/04/12 19:31

こんばんは

回答がつかないようなので・・・

休憩が必ずしも規則的とは言えないので、実時間を求めるのに順に計算しなければならないのが面倒ですね。
もう少し、スマートな方法がありそうな気もしますけれど、休憩時間表と併せてベタに計算する方法の例をご参考までに挙げておきます。


添付図では、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 …

この回答へのお礼

ご回答いただきありがとうございました。
ちょっとまだ理解できておらず・・。ゆっくり解読します。

お礼日時：2021/04/18 23:00