エクセルで、土日祝日と休憩時間を除いて、開始日時、作業時間から
終了日時をだしたいのですがどのようにしたらよいかわかりません。
例えば
開始日時: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使用です。
どのようにしたらよいでしょうか・・。
作業用セルを使って複数回にわけて結果をだす数式でもかまいません。
プログラムを記述しないとできませんでしょうか・・。
よろしくお願いいたします。
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ではないでしょうか?
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さんの回答と同様に全ての場合を検証している訳ではありませんので、正しくない結果を返す場合があるかもしれません。
その場合はご容赦ください。ご指摘いただければ可能な限り修正します。
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 …
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
このQ&Aを見た人はこんなQ&Aも見ています
-
準・究極の選択
「年収1000万円で一生カレーライス」か 「年収180万円で毎日何でも食べ放題」 あなたはどちらを選びますか?
-
フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
あなたが普段思っている「これまだ誰も言ってなかったけど共感されるだろうな」というあるあるを教えてください
-
映画のエンドロール観る派?観ない派?
映画が終わった後、すぐに席を立って帰る方もちらほら見かけます。皆さんはエンドロールの最後まで観ていきますか?
-
海外旅行から帰ってきたら、まず何を食べる?
帰国して1番食べたくなるもの、食べたくなるだろうなと思うもの、皆さんはありますか?
-
天使と悪魔選手権
悪魔がこんなささやきをしていたら、天使のあなたはなんと言って止めますか?
-
Excelの時間計算で経過時間から特定の時間を引く方法
Excel(エクセル)
-
エクセルで勤務時間を出したい。 土日、祝日と18時以降、朝9時までを取り除きたい
Excel(エクセル)
-
エクセルで休憩時間を除く
その他(Microsoft Office)
-
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・人生のプチ美学を教えてください!!
- ・10秒目をつむったら…
- ・あなたの習慣について教えてください!!
- ・牛、豚、鶏、どれか一つ食べられなくなるとしたら?
- ・【大喜利】【投稿~9/18】 おとぎ話『桃太郎』の知られざるエピソード
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルの質問です。 F列からL...
-
作成した数式を値として表示し...
-
エクセルの文字が途中から消える
-
条件付き書式設定で罫線を引き...
-
Excel関数について教えてくださ...
-
Excelの警告について
-
Excel関数について教えてくださ...
-
ワークシートに出現したこの画...
-
エクセルのセル内に分数などの...
-
タイムスタンプとテキストから...
-
EXCELの散布図で日付が1900年に...
-
エクセルでファイルの最終更新...
-
シートの情報を別のシートへま...
-
マクロの処理が遅くなった
-
エクセルの数式バーのフォント...
-
エクセルの「条件付き書式」を...
-
エクセル日付 文字列の関数がエ...
-
Excelでの文字色
-
Excelの数字の前に入っている空...
-
Excelについて教えてください。...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excelの警告について
-
Excelで数値を時間数に変換する...
-
エクセルの数式バーのフォント...
-
エクセルで数字の組み合わせを...
-
エクセルを使用して、円周率を...
-
Excelで特定の文字列が含まれて...
-
Excel 対象のセルに入力が無い...
-
任意の値が存在する行に名前を...
-
エクセルでファイルの最終更新...
-
index関数の説明をお願いします。
-
条件付き書式でやりたいのですが
-
重複しない値を取り出したい
-
【ExcelVBA】UTF-8(BOM無)でC...
-
【マクロ】マクロが割当てされ...
-
エクセル IF計算式?でしょうか?
-
エクセルで曜日を入れたい
-
表中の指定した条件の文字列を...
-
【Excel】版が同じ事を示す番号...
-
EXCELの散布図で日付が1900年に...
-
Excelについて。Excelに縦1列に...
おすすめ情報