Excel：開始日時から終了日時の表示

質問

例えば、「シート1」の「C3～C6」に日時が入力されているとします。
そして、「D3～D6」に開始日時、「E3～E6」に終了日時、「F3～F6」に「手続き可能中」などの文字を表示したいと思っています。

ただし、条件があります。

■開始日時について
「D3」に表示する日時：「C3」に入力されている日時から、3ヶ月前（例えば、「C3」に2016年4月30日と入力されているとすれば2016年2月1日となります。）からが「開始日時」となります。開始日時の開始日は必ず「1日」となります。
「D4」に表示する日時：「C4」に入力されている日時から、3ヶ月前（例えば、「C4」に2016年4月30日と入力されているとすれば2016年2月1日となります。）からが「開始日時」となります。開始日時の開始日は必ず「1日」となります。
「D5」に表示する日時：「C5」に入力されている日時から、1ヶ月前（例えば、「C5」に2016年4月30日と入力されているとすれば2016年4月1日となります。）からが「開始日時」となります。開始日時の開始日は必ず「1日」となります。
「D6」に表示する日時：有効期間満了年の誕生日の1ヶ月前。例えば、誕生日が4月15日なら月日のみ3月15日。

ただし、下記の「■表示してはいけない（取り除く）「開始日時」、「終了日時」について」参照。

■終了日時について
「E3」に表示する日時：基本的には「C3」を表示する。
「E4」に表示する日時：基本的には「C4」を表示する。
「E5」に表示する日時：基本的には「C5」を表示する。
「E6」に表示する日時：基本的には「C6」を表示するが、土曜日、日曜日、祝日（振替休日を含む）、12月29日～1月3日（年末年始）に当たるときは、これらの日の翌日までです。

ただし、下記の「■表示してはいけない（取り除く）「開始日時」、「終了日時」について」参照。

■表示してはいけない（取り除く）「開始日時」、「終了日時」について
「D3」に表示してはいけない日時：土曜日、日曜日、祝日（振替休日を含む）、12月29日～1月3日（年末年始）
「D4」に表示してはいけない日時：土曜日、日曜日、祝日（振替休日を含む）、12月29日～1月3日（年末年始）
「D5」に表示してはいけない日時：土曜日、日曜日、祝日（振替休日を含む）、12月29日～1月3日（年末年始）
「D6」に表示してはいけない日時：土曜日、祝日（振替休日を含む）、12月29日～1月3日（年末年始）

「E3」に表示してはいけない日時：土曜日、日曜日、祝日（振替休日を含む）、12月29日～1月3日（年末年始）
「E4」に表示してはいけない日時：土曜日、日曜日、祝日（振替休日を含む）、12月29日～1月3日（年末年始）
「E5」に表示してはいけない日時：土曜日、日曜日、祝日（振替休日を含む）、12月29日～1月3日（年末年始）
「E6」に表示してはいけない日時：土曜日、祝日（振替休日を含む）、12月29日～1月3日（年末年始）

■その他
01. いずれも関数のみで表示すること。
02. 祝日は、他のシートとすること。「シート2」とする。
03. 関数式の祝日の設定は、増減・変更の可能性があるためその部分の関数式を変更しなくても永久に使えるようにすること。

条件は以上です。

■「F3～F6」について
「F3～F6」については、「開始日時」より1日前まででは「手続き必要なし」、「開始日時」から「終了日時」までの期間までなら「手続き可能中」、「終了日時」より1日後以降なら「期限切れ」という文字列を表示したいです。

Excel2010です。
これらの条件を満たした関数式を教えてください。
できれば、関数式の具体的な解説もいただけるとありがたいです。

難しいとは思いますが、回答よろしくお願いします。

回答No.7

kagakusuki

後、

>これは、「C6」にすでに入力されています。ただし、誕生日+1ヶ月後という形ですが。

との事ですが、それですと誕生日が1月29日の方と、2月1日の方は、C6セルに入力される日付がともに3月1日になりますから、誕生日が1月29日の方の場合であってもD6セルに表示する日付を1月1日にするしかなくなりますし、同様に誕生日が1月30日、31日の方の場合にもD6セルに表示する日付を1月2日、3日にするしかなくなりますが、それで宜しいでしょうか?

お礼

回答ありがとうございます。

>との事ですが、それですと誕生日が1月29日の方と、2月1日の方は、C6セルに入力される日付がともに3月1日になりますから、誕生日が1月29日の方の場合であってもD6セルに表示する日付を1月1日にするしかなくなりますし、同様に誕生日が1月30日、31日の方の場合にもD6セルに表示する日付を1月2日、3日にするしかなくなりますが、それで宜しいでしょうか?

■C6に入力されている日付について
誕生日が1月29日の方ですと、C6には2月29日（うるう年の場合。うるう年でない場合、2月28日）が入っています。
誕生日が2月1日の方ですと、C6には3月1日が入っています。

■D6に表示する日付について
誕生日が1月29日の方ですと、条件なしなら12月29日なのですが、「■表示してはいけない（取り除く）「開始日時」、「終了日時」について」の条件より、12月29日～1月3日を取り除かなければならないため、1月4日が表示結果となります。
誕生日が2月1日の方ですと、条件なしなら1月1日なのですが、「■表示してはいけない（取り除く）「開始日時」、「終了日時」について」の条件より、12月29日～1月3日を取り除かなければならないため、1月4日が表示結果となります。

開始日（「D3～D6」）の表示は、「■表示してはいけない（取り除く）「開始日時」、「終了日時」について」の項目にある条件付きで、その「翌日」を表示させます。
また、D6とE6の開始日と終了日についてですが、
誕生日が1月29～31日の方の場合、開始日が12月28日、終了日が2月28日（うるう年の場合、2月29日）となります。
誕生日が31日の方は、開始日が誕生日の前月の末日、終了日が誕生日の翌月の末日となります。例えば、誕生日が3月31日の場合、開始日が2月28日（うる年の場合、2月29日）、終了日が4月30日となります。
ただし、条件を忘れないでください（「■表示してはいけない（取り除く）「開始日時」、「終了日時」について」参照。）。

説明不足で申し訳ありません。
よろしくお願いします。

回答No.6

kagakusuki

済みません、前回の回答で確認し忘れていた事が御座いました。

>「F3～F6」に「手続き可能中」などの文字を表示したい

との事ですが、どの様な条件を満たした場合に「手続き可能中」と表示させれば宜しいのでしょうか?
　おそらく、現在の日時がD列～E列に入力されている期間内である場合に「手続き可能中」と表示させれば良いのではないかと想像しておりますが、それで宜しいのでしょうか?
　只、一口にD列～E列に入力されている期間内と申しましても、「以上」と「を超えて」の違いや、「以下」と「未満」の違いがあるのと同様に、「開始日時」当日に取引が出来るパターンと出来ないパターンの違いや、「終了日時」当日に取引が出来るパターンと出来ないパターンの違いも、もしかしますとあるかも知れません。
　常識的には「開始日時」当日や「終了日時」当日の両方とも「手続き可能中」に該当するものと想像しておりますが、それで宜しいのでしょうか?
　後、質問者様はそこまで細かい事を要求しておられる訳ではと思いますが、「手続き」という事は、その手続きを行う場所が開業している時間帯でなければ手続きが行えない訳で、「開始日時」と言っても日付が変わった直後の午前0時から手続きが可能になる訳ではないと思いますし、「終了日時」当日と言っても日付が変わる直前の23時59分まで手続きが可能という訳でもないと思います。
　ですから、例えば、現在の日時が「開始日時」当日の午前9:30以降(午前9:30丁度を含む)～「終了日時」当日の16:55直前(16:55は含まない)までの範囲内において、「手続き可能中」と表示させる様にする事も出来ます(但し、「開始日時」～「終了日時」の途中の日に関しては、例え真夜中であっても「手続き可能中」と表示されてしまいます)が、開始時刻や終了時刻をそれぞれ何時何分にすれば良いのかという事も、宜しければ御教え頂けないでしょうか?

お礼

回答ありがとうございます。

>おそらく、現在の日時がD列～E列に入力されている期間内である場合に「手続き可能中」と表示させれば良いのではないかと想像しておりますが、それで宜しいのでしょうか?

はい。
「D列」（当日）から「E列」（当日）までで、「手続き可能中」です。

また、時間帯のことまでは考えておりません。あくまで年月日だけです。時刻まで範囲に入れる必要はありません。
そこまで考えてくださってありがとうございます。

よろしくお願いします。

回答No.5

Nouble

補足させてください
振り替えかた
此を決める
必要があります

例えば
ハッピーマンデー
に、するか
遡って休み、
つまり週末を休み
に、するか

法的根拠の無い
土曜の扱い
を、どうするか

等です。

此等は
企業、団体毎に
異なる
と、思われます。

売り物
と、いった
のは、

難しさ
では、なく

商業ベースに
乗るかどうか

此で利益が
出るかどうか
です、

此、
完成させたら

カレンダー印刷会社
できそうです
よね？

ケチ臭いかな？

お礼

回答ありがとうございます。

「■表示してはいけない（取り除く）「開始日時」、「終了日時」について」は、条件として変わりありません。
ただし「■その他」の、「03. 関数式の祝日の設定は、増減・変更の可能性があるためその部分の関数式を変更しなくても永久に使えるようにすること。」とします。
土曜日は必ず取り除くようにします。
振替休日については、現状の法で構いません。

また説明不足でしたが、
開始日（「D3～D6」）の表示は、「■表示してはいけない（取り除く）「開始日時」、「終了日時」について」の項目にある条件付きで、その「翌日」を表示させます。
終了日（「E3～E6」）の表示は、「■表示してはいけない（取り除く）「開始日時」、「終了日時」について」の項目にある条件付きで、その「前日」を表示させます。

よろしくお願いします。

補足

申し訳ありません、補足説明させていただきます。

終了日（「E3～E6」）の表示は、「■表示してはいけない（取り除く）「開始日時」、「終了日時」について」の項目にある条件付きで、その「前日」を表示させます。
と、「前日」を表示すると記載しましたが、
「E6」のみ、基本的には「C6」を表示するが、土曜日、日曜日、祝日（振替休日を含む）、12月29日～1月3日（年末年始）に当たるときは、これらの日の翌日までです。
「これらの日の翌日まで」が表示結果となります。

回答No.4

kagakusuki

確認したい事が幾つかあります。

>「D6」に表示する日時：有効期間満了年の誕生日の1ヶ月前。例えば、誕生日が4月15日なら月日のみ3月15日。

という箇所の所に記述されている「誕生日」とは、一体何の事なのでしょうか?
　その"誰かの"誕生日の情報は一体どこに存在しているのでしょうか?(もし、Excel上にない場合には、一体どうやってExcelに処理させると仰るのでしょうか?)

>表示してはいけない日時：土曜日、日曜日、祝日（振替休日を含む）、12月29日～1月3日（年末年始）

とある事に関連する話なのですが、例えば12月29日～1月3日の期間内に土日祝日が含まれている場合や、祝日が土曜日となる場合などにおいても、一般的なカレンダーに赤字で表示されている振替休日以上に(表示してはならないとされる独自の)振替休日が増える事は無いと考えて宜しいのでしょうか?

　D3～D5セルに表示させる「開始日」の条件に、

>開始日時の開始日は必ず「1日」となります。

>表示してはいけない日時：土曜日、日曜日、祝日（振替休日を含む）、12月29日～1月3日（年末年始）

とありますが、これらの条件に素直に従った場合、開始日が土曜日、日曜日、祝日（振替休日を含む）、12月29日～1月3日（年末年始）になる場合には何も表示しない様にするという事になりますが、それで宜しいのでしょうか?
　土曜日、日曜日、祝日（振替休日を含む）、12月29日～1月3日（年末年始）に該当する期間の翌日の日付を表示させるという事ではないのでしょうか?

　同様に、D6セルに表示させる「開始日」やE3～E5セルに表示させる「終了日」に関しても、

>土曜日、日曜日、祝日（振替休日を含む）、12月29日～1月3日（年末年始）に当たるときは、これらの日の翌日

という条件が付いておりませんが、これらに関しても土曜日、日曜日、祝日（振替休日を含む）、12月29日～1月3日（年末年始）になる場合には何も表示しない様にした方が宜しいのでしょうか?

お礼

回答ありがとうございます。

>という箇所の所に記述されている「誕生日」とは、一体何の事なのでしょうか?
　その"誰かの"誕生日の情報は一体どこに存在しているのでしょうか?(もし、Excel上にない場合には、一体どうやってExcelに処理させると仰るのでしょうか?)

これは、「C6」にすでに入力されています。ただし、誕生日+1ヶ月後という形ですが。

>とある事に関連する話なのですが、例えば12月29日～1月3日の期間内に土日祝日が含まれている場合や、祝日が土曜日となる場合などにおいても、一般的なカレンダーに赤字で表示されている振替休日以上に(表示してはならないとされる独自の)振替休日が増える事は無いと考えて宜しいのでしょうか?

そうですね。
基本的には、土曜日、日曜日、12月29日～1月3日は取り除きます。
また、「祝日（振替休日を含む）」は時代によって変更もありえるのですが、kagakusukiさんのおっしゃる通り、
「一般的なカレンダーに赤字で表示されている振替休日以上に(表示してはならないとされる独自の)振替休日が増える事は無い」と考えてよろしいです。

そして、「D3～D6」の開始日、「E3～E6」の終了日は必ず表示してください。
「■表示してはいけない（取り除く）「開始日時」、「終了日時」について」というのは、正しくは「取り除く」という意味で「開始日」、「終了日」は必ず表示させるようにします。

>土曜日、日曜日、祝日（振替休日を含む）、12月29日～1月3日（年末年始）に該当する期間の翌日の日付を表示させるという事ではないのでしょうか?

その通りです。「開始日」については「土曜日、日曜日、祝日（振替休日を含む）、12月29日～1月3日（年末年始）に該当する期間の翌日の日付を表示させる」ということです。
例えば、「D3」の「開始日」ですが、「■開始日時について」の「「D3」に表示する日時：「C3」に入力されている日時から、3ヶ月前」というのが、たまたま2015年2月1日であった場合、2015年2月1日は日曜日のため、取り除く条件に当てはまるため表示結果は、2015年2月2日となります。
開始日（「D3～D6」）の表示は、「■表示してはいけない（取り除く）「開始日時」、「終了日時」について」の項目にある条件付きで、その「翌日」を表示させます。
終了日（「E3～E6」）の表示は、「■表示してはいけない（取り除く）「開始日時」、「終了日時」について」の項目にある条件付きで、その「前日」を表示させます（例えば、2015年10月31日を表示させるところを2015年10月31日は、土曜日のため、表示結果は2015年10月30日となります。）。

説明不足で申し訳ありません。

補足

申し訳ありません、補足説明させていただきます。

終了日（「E3～E6」）の表示は、「■表示してはいけない（取り除く）「開始日時」、「終了日時」について」の項目にある条件付きで、その「前日」を表示させます。
と、「前日」を表示すると記載しましたが、
「E6」のみ、基本的には「C6」を表示するが、土曜日、日曜日、祝日（振替休日を含む）、12月29日～1月3日（年末年始）に当たるときは、これらの日の翌日までです。
「これらの日の翌日まで」が表示結果となります。

回答No.3

Nouble

やってやれなくは
ないでしょう

が、
売り物レベル
かも

相手が
無償協力
で、ある事

此を、お忘れなく

ところで、
振り替え休日の
施行ルールは
どの様にする
の、ですか？

http://ja.m.wikipedia.org/wiki/%E6%8C%AF%E6%9B%BF%E4%BC%91%E6%97%A5

http://matome.naver.jp/m/odai/2134737463707530301

http://blog.livedoor.jp/dqnplus/archives/1723437.html

補足をお願いします。

お礼

回答ありがとうございます。

売り物レベルですか・・・。そこまで難しいとは。

振替休日のルールは、例えば別のセルにプルダウンリスト（ドロップダウンリスト)を用意し、「なし」、「次の日」、「前の日」といったような選択肢によって変更ができるようにするとかでしょうか・・・。

すいません、自分にはそれぐらいしか思い浮かばないのですが。

回答No.2

kagakusuki

>例えば、「シート1」の「C3～C6」に日時が入力されているとします。

との事ですが、そのC列に入力される日時とは、何年何月何日から何年何月何日までの日付の事なのでしょうか?
　何故こんな事を訊ねるのかと申しますと、「表示してはいけない日時」の中に祝日や振替休日が含まれているからです。
　祝日や振替休日を表示しない様にするためには、祝日や振替休日の一覧表を用意しておき、「開始日時」や「終了日時」の暫定値が、その一覧表の中に存在するのかどうかを判定しなければなりません。
　そのため、例えばC列に入力される日時の中で最も古い日付が1901年で、最も新しい日付が西暦9999年だった場合には、8099年分の祝日の一覧表を用意しなければなりません。
　ですから、祝日を求めておかねばならない期間が、C列に入力される日時の中で最も古い日付に対して、その前年の何月から始まって、何年後の何月までになるのかという事が判らなければ、回答のしようが無い訳です。

　又その他にも理由はあります。
　例えば今年の8月11日は平日ですが、来年から8月11日は「山の日」という祝日になります。
　又例えば、戦中・戦前の祝日は、現在とは全く異なっておりました。
　この例から判ります様に、祝日の決め方が時代によって変わって来る場合があります。
　そのため、C列に入力される日時の範囲が広ければ広い程、入力される年ごとの祝日の決まり方が異なって来る恐れが高く、その複数の異なった決まりの全てに対応させるために、より複雑な関数としなければならなくなりますし、そもそも、どの年の決まりからどの年の決まりにまで対応させねばならないのかが判らなければ、関数を組む事が出来ません。

　後それから、「春分の日」と「秋分の日」に関しては、太陽が天球上の赤道面を通過する瞬間が何月何日の何時何分何秒になるのかという事を、国立天文台が天文観測のデータを基にして計算で求めて、そのデータを基にして政府が閣議を開いて「来年の春分の日や秋分の日は、それぞれ何月何日とする」という事を決定するものですので、閣議でまだ決定されていない再来年以降の春分や秋分の日が何月何日になるのかは、まだ決まってはおりません。
　尤も、国立天文台が計算した春分や秋分の"瞬間"があった日付と、閣議で決定された春分の日や秋分の日が異なった事は皆無でしたので、春分や秋分の"瞬間"が何月何日に起こるのかという事が判れば、それを「春分の日」や「秋分の日」と見做しても構わないと思われます。
　しかし、春分や秋分は地球の動きによって生じる天文現象であり、地球の動きは他の天体の引力の影響を受けて微妙に変化するため、おそらくはスーパーコンピューターの類を使ってシミュレーションして時刻を予想していると思われる国立天文台の推算時刻ですら、数分程度の誤差は免れない様です。
　ましてや単純計算しか出来ないExcelに計算させたのでは、誤差が無視しえないほど大きくなる恐れが高いと思われます。
　そのため、もしExcelの計算で求めた春分や秋分の瞬間の時刻が、例えば23時48分や0時20分などといった日付が変わる0時丁度に近い時刻となった場合、他の天体の影響によって、実際の春分や秋分の日が別の日付となる恐れがある訳です。
　ですから、「春分の日」や「秋分の日」に関しては、国立天文台等がサイト上で発表しているデータを参照して、一覧表に手入力するしか無い訳で、そのデータも通常、来年のものまでしか発表されていないため、再来年以降に関しては信頼出来る日付は解らないのです。
　そのため、もしC列に再来年以降の日付が入力される恐れもある場合には、祝日や振替休日に関わる計算が不確実なものになります。

お礼

回答ありがとうございます。

時間は関係なく、正しくは「開始日」（×開始日時）、「終了日」（×終了日時）でした。

>との事ですが、そのC列に入力される日時とは、何年何月何日から何年何月何日までの日付の事なのでしょうか?

一応、現在日（2015年5月9日）からExcel2010の仕様の最終日（9999年12月31日）としておきます。

自分としましても、時代などによって祝日が変更されるため、どのように式を作っていいか分からず困り果てていました。
「春分の日」と「秋分の日」はそのように決められているんですね。
どうやら祝日を取り除くのは不可能なようですね。
この式を作るのは諦めます。

ありがとうございました。

回答No.1

msMike

》 これらの条件を満たした関数式を教えてください
随分長々と条件ばかりお書きだけど、貴方自身で考えた式は一つもご座んせんの？
「此処までは出来たけど、此処の処で行き詰まっている」とかさ？
例えば、開始日時=EOMONTH(日時,-3)+1 とか理解できますか？

それから、開始日時、終了日時とか言ってるけど、ハナシを聞くとそれぞれ開始日、終了日の間違いではないかと。

お礼

回答ありがとうございます。

>随分長々と条件ばかりお書きだけど、貴方自身で考えた式は一つもご座んせんの？
「此処までは出来たけど、此処の処で行き詰まっている」とかさ？
例えば、開始日時=EOMONTH(日時,-3)+1 とか理解できますか？

申し訳ありませんが、初心者なもので簡単な関数、SUMやIF程度しか分かりません。
それと、時間は関係ないですね。
正しくは「開始日時」、「終了日時」ではなく、「開始日」、「終了日」です。

「回答No.2」のkagakusukiさんの回答を読ませていただきまして、どうやらこの関数式を作るのは難しいようですね。

ありがとうございました。