【スプレッドシート】指定の日付より2か月前、1か月半前に書式設定でアラートを出したい。

スプレッドシートで日付の前にアラートを出す、書式設定をしたいのですが1か月半前については
以下の場合、1.5でよろしいのでしょうか？
何卒、ご教授願います。

※B列が基準日が入っている列
=EOMONTH(B:B,-2)<TODAY()

No.3 ベストアンサー 回答者： goomania 回答日時： 2023/02/14 00:09

ご質問のタイトルに「指定の日付より2か月前、1か月半前に･･･」とありますが、この指定日というのは毎月決まった日(例えば毎月15日とか)なのでしょうか?それとも、1日だったり、10日だったり、25日だったりとバラバラなのでしょうか?

仮にバラバラだとすると、指定日、例えばB1セルが「2023/2/1」の場合、=EOMONTH(B1,-2)という数式を使うと「2022/12/31」が返ります。これだと指定日まで1月か月と1日しかありません。また、＝EOMONTH(B1,-2)+DAY(EOMONTH(B1,-2))/2という数式を使うと「2023/1/15」が返ります。同様に指定日まで半月しかありません。

ご質問者のご希望は2か月および1か月半前だったはずです。この点を考えると最初のEOMONTH関数はEDATE関数の誤りなのではないかと思います。
従って、添付画像のように、

=EOMONTH(B1,-2)<TODAY()･･･(1)
=EDATE(B1,-2)<=TODAY()･･･①
(1)を①に修正し、

=EOMONTH(B1,-2)+DAY(EOMONTH(B1,-2))/2<TODAY()･･･(2)
=INT(EDATE(B1,-2)+DAY(EOMONTH(B1,-2))/2)<=TODAY()･･･②
(2)を②に修正するということになります。

こうすると①では指定日の2か月前の応当日からアラート表示(添付画像では淡青塗りつぶし)、②では指定日の2か月前の応当日から当該月の半月分日数を経過した日(つまり1か月半前)からアラート表示という意味の数式になります。

②の数式でINT関数を使用している理由を説明します。DAY(EOMONTH(B1,-2))/2は月の日数を2で割って半月の日数を求める数式ですが、月の日数は28、29、30、31の四種類あります。このうち奇数の日数である29、31の場合、2で割ると整数にならず、「.5」の小数点以下がでます。日付はシリアル値で整数ですから、TODAY()と一致させるには整数化する必要があります。
このため、INT関数を使って0.5は結果として切捨ています。
つまり、29日の月は2か月前から14日経過でアラート、31日の月は2か月前から15日経過でアラート表示になります。

なお、切捨てではなく、29日のときは15日、31日のときは16日経過したら「半月」としたいという場合は、②の数式を

=ROUNDUP(EDATE(B1,-2)+DAY(EOMONTH(B1,-2))/2,0)<=TODAY()･･･③
という数式にすることになります。

さらにいえば、上記数式は文字通り「指定の日付より2か月前、1か月半前に･･･」という考えを数式化したものですが、今日を基準にすると「④今日の2か月後が指定日以降ならアラート」「⑤今日の1か月半後が指定日以降ならアラート」と考えることもできます。この考えで数式を作ると

=EDATE(TODAY(),2)>=B1･･･④
=INT(EDATE(TODAY(),2)-DAY(EOMONTH(TODAY(),2))/2)>=B1･･･⑤

のようになります。

この回答へのお礼

goomania 様

この度はご丁寧にご教授いただきましてありがとうございます。
大変わかりやすく、助かりました！

>ご質問のタイトルに「指定の日付より2か月前、1か月半前に･･･」とありま>すが、この指定日というのは毎月決まった日(例えば毎月15日とか)なのでし>ょうか?それとも、1日だったり、10日だったり、25日だったりとバラバラ>なのでしょうか?
→この日付の部分は契約終了日の日付となる為、各月の月末の日付が入っている部分になります。

確かに仰る通り、月によって日数が異なる為ズレていってしまいますね。。
ご教授いただきました数式にて無事解決いたしました！！

INT関数をなぜ使用するのか？指定日ではなく、今日を基準にする場合など、パターン別にもご教授いただき、大変勉強になりました。
本当にありがとうございました！

お礼日時：2023/02/14 11:07

No.2 回答者： enunokokoro 回答日時： 2023/02/13 11:15

No.1の回答者です。

ちょっと気になる部分があることと、数式の修正を。

※B列が基準日が入っている列
=EOMONTH(B:B,-2)<TODAY()
これでアラートが出るのですか？
条件付き書式を使うなら、範囲をB:Bにして、
　=EOMONTH(B1,-2)<TODAY()
で指定することで対応しますよね。

上記で対応するなら、
　=EOMONTH(B1,-2)+DAY(EOMONTH(B1,-2))/2<TODAY()
で済むはずです。

この回答へのお礼

ご丁寧にご教授いただきましてありがとうございます。
勘違いをしておりました＞＜
ご指摘感謝いたします！
再度、対応してみます。

お礼日時：2023/02/13 11:55

No.1 回答者： enunokokoro 回答日時： 2023/02/13 10:15

EOMONTH関数を使いたいのですか？

1か月半前を1.5にしても、小数点以下は切り捨てられます。
https://support.google.com/docs/answer/3093044?h …
これはExcelでも同じです。
https://support.microsoft.com/ja-jp/office/eomon …

2か月前の月末の半分の日付を足せば、1か月半になるので、
　=EOMONTH(B1,-2)+DAY(EOMONTH(B1,-2))/2
で日付を取り出せます。
　=EOMONTH(B:B,-2)+DAY(EOMONTH(B;B,-2))/2<TODAY()
などで対応してみてはいかが。

この回答へのお礼

ご教授いただきましてありがとうございます！
特段、EOMONTH関数を使いたいわけではなく、いろいろ調べていたところその数式にたどり着きました！

2か月前と1か月半前を分けて書式設定したい場合は、以下と認識いたしました！

・2か月前=EOMONTH(B:B,-2)<TODAY()
・1か月半前=EOMONTH(B:B,-2)+DAY(EOMONTH(B;B,-2))/2<TODAY()

ちなみにその際の書式ルールは、次より前の日付、正確な日付の設定で問題ないでしょうか？

お礼日時：2023/02/13 10:56