＜条件追加/スプレッドシート＞指定の日付より2か月、1.5か月前に書式設定アラートを出したい

Question

おはようございます。

先日は以下質問にてご教授いただきましてありがとうございました。
https://oshiete.goo.ne.jp/qa/13351401.html（№3のご回答にて解決済）

ただ、それに追加で以下の書式設定を追加したいのです。

＜追加条件＞
当月末になっている（2023/02/28）日付はグレーにしたい。

その場合、どのような書式設定を追加、もしくはご提示いただいた数式に条件を追加した数式に修正が必要なのか？

何度も申し訳ございませんが、何卒ご教授願いますm(__)m

goomania · Accepted Answer

最初にお詫び申し上げます。
No.4、5、6の回答にある③の数式の中の「+DAY(EOMONTH(B5-2))/2,0)」という部分は「+DAY(EOMONTH(B5,-2))/2,0)」の誤りでした。
ご質問者が「お礼」に投稿されたものをそのまま再投稿してしまったため、誤りに気付きませんでした。お詫びして訂正いたします。

さて、

>その当月末という条件の対象とする日付をB1に設定しているので･･･

という説明は初めて聞きました。いままでのご説明や添付画像では一度も登場していない設定です。

重ねての指摘で失礼ですが、表の構造の全体像をお示しにならないので、部分的に情報を小出しにされても回答のしようがありません。

また、ご質問者は数式の意味をご理解されていないようです。
そもそも回答No.4の修正案(1)
①=EOMONTH(TODAY(),0)=B5
という数式の「EOMONTH(TODAY(),0)」の部分は「今月末日」を意味します。従って、①は「今月末日とアラート設定範囲の開始セル「B5」の日付が一致している」というアラート発動条件だということになります。
これをB5～B12の範囲に適用するわけですから、「B5～B12セルに今月末日と一致している日付があればその日付がグレーになる」ということになり、B1セルは関係ありません。

添付画像(1)をご覧ください。
ご質問者が補足で掲示された日付の内容とほぼ同じものに、前回ご質問の条件付き書式を設定してみました。ご質問者の設定はオレンジの優先順位が2番目になっており、これは誤りです。
黄色が2番目、オレンジが3番目でなければいけません。前回のご質問に回答してから日数が経っているので、2023/03/31まで1か月半を切ってしまいました。
つまり2023/03/31は「黄色」に塗られなければいけません。また、当月末日である「2023/02/28」はグレーに塗られており、カスタム数式が有効に機能していることがわかります。
ご質問者の例示には登場しない「2023/04/20」という日付をB13に入れ、条件付き書式の設定範囲をB13まで拡大してみました。
ちゃんとオレンジ色に塗られ、2か月前になったらアラート表示のカスタム数式も有効に機能していることが判ります。

ただ、ご質問者の補足画像を見て気になったのが日付が全て左寄せになっていることです。これはご質問者があえて表示形式左寄せにしたということなのでしょうか?
そうではなく、意識していないが、左寄せになっていたということなのでしょうか?
意識せず左寄せになったとすると、日付に見えているものが「文字」である可能性があります。この場合当然のことながら条件付き書式のカスタム数式が機能しません。

添付画像(2)をご覧ください。
同じ条件付き書式を設定しているのですが、「2023/02/28」がグレーになっていません。日付に見えているのが「文字」だからです。
では何故、黄色とオレンジは機能しているのでしょうか?
黄色とオレンジはB列の値が関数の中で使われているからです。関数によっては文字列でも正しく結果を返すものがあり、EDATE関数、EOMONTH関数とも関数の中の日付が文字列であっても正しい結果を返します。
ところが①=EOMONTH(TODAY(),0)=B5の数式はB5は関数の中で使われている訳ではありません。このため①の数式のみ有効にならないというわけです。
そこで結論です。この場合No3の回答で指摘した(2)のケースで日付に問題があるわけです。ですから、①の数式を

①=EOMONTH(TODAY(),0)=B5*1

という数式に替えてください。これで万一、B列の値が文字列であっても、添付画像(3)のように条件付き書式が機能するはずです。

なお、(あまり意味があるとは思えませんが)今月末日を敢えて指定するためB1セルに「2023/02/28」と入力させ、カスタム数式の「EOMONTH(TODAY(),0)」を「B1」に替えて、①'=$B$1*1=B5*1のようにするという考えもあります(※B1あるいはB5以下のセルのいずれかが文字列の可能性があるので*1を入れています)。そこで、試しに①'の数式に入替してみました。添付画像(4)をご覧ください。
ご覧のように同じ結果になることが判ります。つまり、「EOMONTH(TODAY(),0)」が「今月末日」を意味しているので、B1に「2023/02/28」があるなら、これも「今月末日」ですから同じ結果になるのは当たり前です。

goomania · Answer

>①=EOMONTH(TODAY(),0)=B5

>②=EDATE(B5,-2)<=TODAY()
>③=ROUNDUP(EDATE(B5,-2)+DAY(EOMONTH(B5-2))/2,0)<=TODAY()
>上記のB5はB5:B12と対象セルを選択した際、選択開始セルの「B5」と
>いう認識でよろしいでしょうか？

というご質問ですが、そもそも色を付ける(アラート表示する)列はどの列なのでしょうか?

(1)B列の日付を基準にE列に色つけ(アラート表示)をする
(2)B列の日付を基準にB列に色つけ(アラート表示)をする

ご質問は(2)を行いたいという意味でよろしいでしょうか?
補足に投稿記載された画像だと(1)とも読み取れるのでお尋ねしています。

上記(2)を前提とするならば、対象セルを選択した際、選択開始セルの「B5」という認識でOKです。
条件付き書式を設定し、カスタム数式を適用するセルの開始セル(選択範囲の左上隅)に記述するカスタム数式では「B5」を使用する数式になるという意味です。

goomania · Answer

No.2、3、4です。

「指定の日付より2か月、1.5か月前に書式設定アラートを出したい」というご質問者のご希望を叶えるためのアドバイスを行ってきましたが、ご質問者の表の構造自体はよくわかりません。
ご質問者は条件付き書式の数式をお示しになっていますが、表の構造自体はお示しになっていないからです。

このご質問は前回のご質問の続きだと思うのですが、ご質問者は指定日はB列にあると説明されていましたし、このご質問の「お礼」でも
>B1をB5に変更しましたが列自体は変更ございません
と述べておられます。

にもかかわらず、数式をみると全ての数式がE列を参照するように作成されています。指定日がB列のままなのに、ご質問者がE列を参照する数式を作成してしまう原因を考えてみました。
一番考えられる原因は「E列も日付である」というものです。E列も日付だとするとB列に指定日があるのにE列を参照してしまう可能性があります。
もし、E列も日付だとすると、いままでのアドバイスが根本的に違っている可能性すらあります。

つまり、このご質問は
「E列に存在する日付がB列の指定日から起算して、2か月、1.5か月前のものに書式設定アラートを出したい」･･･(A)
という質問かも知れないということです。

いままでの回答にあるカスタム数式は、他の回答者さんの回答も含めて「指定日」と「今日」との関係を「2か月、1.5か月前」として判定するものです。しかし、仮に、このご質問の真意が(A)だった場合、「今日」は関係ありません。E列と「指定日」との関係を「2か月、1.5か月前」として判定しなくてはいけないからです。

この場合の条件付き書式のカスタム数式は、今までの数式の「TODAY()」をE列に替えることで実現可能です。つまり

①=EOMONTH(E5,0)=B5　グレー
②=EDATE(B5,-2)<=E5　2か月前　オレンジ
③=ROUNDUP(EDATE(B5,-2)+DAY(EOMONTH(B5-2))/2,0)<=E5　イエロー

という数式になります。
老婆心ながら可能性の一つとして投稿しておきます。

goomania · Answer

No.2、3です。

①=EOMONTH(TODAY(),0)<E5
②=EDATE(E5,-2)<=TODAY()
③=ROUNDUP(EDATE(E5,-2)+DAY(EOMONTH(E5-2))/2,0)<=TODAY()
という数式を記述しているとの返信をいただきました。

>B1をB5に変更しましたが列自体は変更ございません

いやいや、基準日が入っている列はもともとB列という話だったのに、①②③ともE列を参照する数式になっていますよ。
また、①の数式はうまくいかなかった場合、原因を探るためにご提案したテスト用の数式になっていますよ。

もし、基準日が入っている列がB列から変更されていないなら、E5:E12の範囲に設定される条件付き書式のカスタム数式は、色を付けるのがE列だとしても、B列を参照することになるので、
修正案(1)
①=EOMONTH(TODAY(),0)=B5
②=EDATE(B5,-2)<=TODAY()
③=ROUNDUP(EDATE(B5,-2)+DAY(EOMONTH(B5-2))/2,0)<=TODAY()
という数式になるはずであり、そうでないと望んだ結果にならないと思います。

逆に、基準日の列がB列からE列に変わったのであれば、
修正案(2)
①=EOMONTH(TODAY(),0)=E5　グレー
②=EDATE(E5,-2)<=TODAY()　2か月前　オレンジ
③=ROUNDUP(EDATE(E5,-2)+DAY(EOMONTH(E5-2))/2,0)<=TODAY()
という数式でご希望が実現するはずです。
※しかし、実現していないので修正案(1)の可能性が高いです。

goomania · Answer

No.2です。

>記載の数式をもう一つ追加して並びを一番上に移動したのですが、
>2か月前の条件(オレンジ色)が反映してしまい、グレーになりません。

うまくいかない原因は、

(1)条件付き書式の設定に問題がある
(2)入力されている日付そのものに問題がある

に分かれます。当方でアドバイスできるのは、(1)のケースです。
添付画像をご覧ください。

当り前のチェックになりますが、もう一度チェックしてください。
①条件付き書式の優先順位(グレイが最上位、オレンジが下)は正しいか
②条件付き書式を設定した範囲はグレイとオレンジが一致しているか?
③条件付き書式に記述したカスタム数式は正しく記述できているか?
④最初のご質問から表を加工してしまい、条件付き書式を設定しようとしいる列番号がB列と異なるものになっていないか?

添付画像でお判りのように、正しく設定すれば、当月月末日がグレイになります。

上記をチェックしても改善しない場合は、グレイの条件付き書式のカスタム数式を以下に替えてみてください。

=EOMONTH(TODAY(),0)<B1･･････(a)

これで、いずれかのセルがグレイに変わるなら(2)のケースです。
つまり、条件付き書式は有効だが日付の入力に問題があるということになります。
残念ながらこちらについては全く情報がないので想像になりますが、(a)の数式を以下の数式に入替すれば正常に動作するかも知れません。

=(EOMONTH(TODAY(),0)-1<B1)*(EOMONTH(TODAY(),0)+1>B1)･･･(b)

全くの想像ですので当たらなくてもご容赦ください。

goomania · Answer

前回のご質問に回答した者です。

>当月末になっている（2023/02/28）日付はグレーにしたい

とのことですが、この場合、新たな条件付き書式の追加が必要です。
添付画像をご覧ください。
画像①のように、既に条件付き書式が設定されている範囲に

=EOMONTH(TODAY(),0)=B1

という条件を追加します。

しかし、これだけではこの条件が効力を発揮しません。
何故なら、当月末までは最大でも30日しかなく、2か月前、1か月半前という条件付き書式が先に有効となってるはずだからです。

従って、画像②のように、後から追加した条件付き書式を既に設定してある条件付き書式より優先順位が高くなるようにしなければいけません。

これには、条件付き書式の「123」と表示されているアイコンの左にマウスをもっていくと、マウスが「矢印の十字」に変わるので、その状態でドラッグ&ドロップで上方向へ移動してください。

chonami · Answer

セルの値→次の値に等しい

=EOMONTH(TODAY(),0)

とかでは？

＜条件追加/スプレッドシート＞指定の日付より2か月、1.5か月前に書式設定アラートを出したい

最初にお詫び申し上げます。

>①=EOMONTH(TODAY(),0)=B5

No.2、3、4です。

No.2、3です。

No.2です。

前回のご質問に回答した者です。

セルの値→次の値に等しい

似たような質問が見つかりました

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング