エクセル 所定の期間に重なっている期間を表示するには？

氏名・開始年月日・終了年月日がリストになっています。

開始から終了の全期間もしくは一部期間が「所定の期間」に重なっている項目がわかるようにしたいです。
「所定の期間」は開始が「2018/10/1」終了が「2019/3/31」のようにセルに入力してあります。
なかには、まだ終了していない為終了年月日が空白の人もいます。
例えば、重なっている人のD列に◯を付けるとかの方法はありますか？

よろしくお願いいたします。

No.5 回答者： mike_g 回答日時： 2019/06/03 15:53

[No.3お礼]へのコメント、

》 例：11/10～3/5の場合であれば11月・3月がそれにあたります
「11/10～3/5の場合」とは、セル B2、C2 が夫々 2018/11/10、2019/03/05 の場合と云うことでしょうか？
それがＹＥＳなら、私が作成した式では此処での添付図のようになりますが、それでＯＫですか？
間違っているなら、何処がどうあるべきとか、●や△は何処に表示されるべきとか、出し惜しみしないで先に述べていただけませんか？
そうしていただけるなら、「本来やりたかったことにかなり」遠いとか近いとか仰る手間が省けると思いますが…

「いかがでしょうか？」

No.4 回答者： grumpy_the_dwarf 回答日時： 2019/06/02 16:08

時間や期間を計算するときの基本「終了と期間の終わりの早い方から、開始と期間の始まりの遅い方を引く。

ただしマイナスにはしない」の形を踏襲します。終了が未入力なら今日の日付になるように改変するだけですが。

=max(0, min(終了日, 期間の終わり, today()) - max(開始日, 期間の始まり))

これが重複した日数。ゼロでなければ"◯"を返せばいい。 =rept("◯", sign(日数の計算)) とか =if(日数の計算, "◯", "") どちらでも同じです。

で、期間の始まりや終わりはたとえば年月だけ指定してあれば1日と同月の末尾ですから、計算可能ですね。

No.3 回答者： mike_g 回答日時： 2019/06/02 15:46

[No.2お礼]へのコメント、

》 本来やりたかったことにかなり近いです
そのコメント、実に意味深でした。
付け焼刃なので冗長に過ぎるとは思いますが、前回の提示式を下記のように修正しておきます。
D7: =IF(AND(C7<>"",C7<B$4),"",IF(MAX($B$4,B7)>$C$4,"",MAX($B$4,B7)))
E7: =IF(D7="","",IF(B$4>MIN($C$4,C7),"",MIN($C$4,C7)))

多分貴方様のがモア・ベターな式だと推察するので、向学の為にそれを是非開陳してください。

さて、
１．添付図において、
￣￣ m
￣￣に書式設定した範囲 F6:K6 の左から
￣￣2018/10/1、2018/11/1、…、2019/3/1
￣￣を入力
２．次式を入力したセル F7 を右方に５列、下方に７行オートフィル
￣￣ =IFERROR(IF((YEAR(F$6)*100+MONTH(F$6)>=(YEAR($D7)*100+MONTH($D7)))*(YEAR(F$6)*100+MONTH(F$6)<=(YEAR($E7)*100+MONTH($E7))),"○",""),"")

貴方が「本来やりたかったことに」はまだまだ程遠いでしょうか？

この回答へのお礼

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

元々想定していた「やりたかったこと」はまさしくこの通りです。
本当に助かります。

欲を言っていいのであれば…
1ヶ月全て重複していない月（例：11/10～3/5の場合であれば11月・3月がそれにあたります）について、下記のとおり判定して表示できると最高です。
・1ヶ月の日数のうち重複日数が半数を超える場合…●
・1ヶ月の日数のうち重複日数が半数以下の場合…△

いかがでしょうか？

お礼日時：2019/06/03 14:42

No.2 回答者： mike_g 回答日時： 2019/06/01 22:46

添付図参照

D7: =IF(MAX($B$4,B7)>$C$4,"",MAX($B$4,B7))
E7: =IF(D7="","",MIN($C$4,C7))

この回答へのお礼

回答ありがとうございます。
本来やりたかったことにかなり近いです。

追加質問です。
F列からK列に10月・11月・・・と表を作り、それぞれの月に1日でも重なっていれば○を表示する、ということは可能でしょうか？
例）次郎は3月のみに○　三郎は1月2月3月に○

これができると手作業での確認が不要になります。

よろしくお願いします。

お礼日時：2019/06/02 12:11

No.1 回答者： tom04 回答日時： 2019/06/01 21:05

こんばんは！

＞重なっている人の・・・
とは
B・C列期間がB4・C4期間内に収まっている人に「○」を表示したい！というコトでしょうか？

そうであればD7セルに
=IF(COUNT(B7:C7)=2*(B7>=B$4)*(C7<=C$4),"○","")

という数式を入れフィルハンドルで下へコピーしてみてください。

※　お示しの配置だとD11セルだけに「○」が表示されます。m(_ _)m

この回答へのお礼

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

「一部期間の重なり」でもOKなので、D8・D9・D10・D12・D14にも○がつくようにしたいです。

お礼日時：2019/06/02 12:04