No.3ベストアンサー
- 回答日時:
ご質問のタイトルに「指定の日付より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関数をなぜ使用するのか?指定日ではなく、今日を基準にする場合など、パターン別にもご教授いただき、大変勉強になりました。
本当にありがとうございました!
No.2
- 回答日時:
No.1の回答者です。
ちょっと気になる部分があることと、数式の修正を。
※B列が基準日が入っている列
=EOMONTH(B:B,-2)<TODAY()
これでアラートが出るのですか?
条件付き書式を使うなら、範囲をB:Bにして、
=EOMONTH(B1,-2)<TODAY()
で指定することで対応しますよね。
上記で対応するなら、
=EOMONTH(B1,-2)+DAY(EOMONTH(B1,-2))/2<TODAY()
で済むはずです。
No.1
- 回答日時:
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()
ちなみにその際の書式ルールは、次より前の日付、正確な日付の設定で問題ないでしょうか?
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) エクセルの数式で教えてください。 2 2023/01/10 09:15
- Excel(エクセル) エクセルの日付から年末尾2桁と月を1月をA~Lに変換したい 3 2022/08/08 10:32
- Excel(エクセル) エクセルの数式で教えてください。 2 2023/04/13 10:55
- Excel(エクセル) エクセルでセルの日付を和暦表示設定にしたらおかしなことに? 3 2022/05/25 11:47
- Excel(エクセル) エクセルについて教えてください。 1 2023/03/03 08:38
- Excel(エクセル) エクセルの数式で教えてください。 2 2022/12/23 14:57
- その他(Microsoft Office) <条件追加/スプレッドシート>指定の日付より2か月、1.5か月前に書式設定アラートを出したい 7 2023/02/16 07:33
- Visual Basic(VBA) vbaで条件付き書式を設定したときの適用範囲について 1 2023/07/17 23:14
- Visual Basic(VBA) 指定月分の顧客データファイルを統合して並べ替え、所定の場所に貼り付ける 3 2022/09/10 07:55
- その他(Microsoft Office) スプレッドシートにて、条件付き書式で複数の単語に合致するセルを着色したいです。 2 2023/04/06 13:25
このQ&Aを見た人はこんなQ&Aも見ています
-
性格の違いは生まれた順番で決まる?長男長女・中間子・末っ子・一人っ子の性格の傾向
同じ環境で生まれ育っても、生まれ順で性格は違うものなのだろうか。家庭教育研究家の田宮由美さんに教えてもらった。
-
<条件追加/スプレッドシート>指定の日付より2か月、1.5か月前に書式設定アラートを出したい
その他(Microsoft Office)
-
Excelに入力した個々の日付の数ヶ月前から自動で色をつける方法を教えてください。
Excel(エクセル)
-
日付が1年以内になると他のセルの色が変わる
Excel(エクセル)
-
-
4
指定した期限が近づくとセルの色が変わる
Excel(エクセル)
-
5
エクセル 期日の半年前になると、セルの色が変わる方法
Windows Vista・XP
-
6
Excelの条件付き書式にて空白セルを除外するには?
Excel(エクセル)
-
7
【スプレドシート】IMPORTRANGE関数とSUMIFSの組み合わせ
その他(Microsoft Office)
-
8
Excel 条件付書式 今日より3日前のセルに色をつけたい
Excel(エクセル)
-
9
E列のセルに数値が入れば(空白でなければ)B列の同じ行のセルに色がつく
その他(Microsoft Office)
-
10
条件付書式【締切日10日前からセルの色をかえる】
その他(ビジネス・キャリア)
-
11
EXCELで条件付き書式で空白セルの時は塗りつぶし無しにする方法
Excel(エクセル)
-
12
「弊社○○のご紹介で初めてご連絡差し上げました」であってますか?
日本語
-
13
EXCEL☆★有効期限2ヶ月前にお知らせする機能
Excel(エクセル)
-
14
Excelで同じ商品名で、かつ日付が古いものを抽出する方法
Excel(エクセル)
-
15
EXCELの条件付き書式で数式を空白と認識してくれる方法
Excel(エクセル)
-
16
エクセルで条件に一致したセルの隣のセルを取得したい
その他(Microsoft Office)
-
17
Excelで来月以降の日付のセルを赤にする条件付き書式設定の仕方を教えてください。
Excel(エクセル)
-
18
条件付き書式で、結果が1行ずれる
Excel(エクセル)
関連するカテゴリからQ&Aを探す
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルでの複数条件下での標...
-
エクセルで年月の合計の関数を...
-
SUMIFとCOUNTIFを合わせたよう...
-
ある一定時間を超えた場合の超...
-
EXCEL 経過年数の平均を求めた...
-
エクセルにて「週」から日付を...
-
エクセルで「ぶら下げ」書式を...
-
Excel:月またぎを含む日数の差...
-
【スプレッドシート】指定の日...
-
エクセルで角度の計算できますか?
-
【エクセル】日付入力
-
Excelの表以外が暗い?
-
エクセル くじ引きの口数が別々...
-
複数連続した列幅や行高を一発...
-
【Excel2003で条件付き書式3つ...
-
エクセルの質問です。
-
Excelで列幅を自動的に調整
-
Excel2007での条件付き書式につ...
-
隣のセルに入力したら自動的に...
-
スプレッドシート、Excelでの数...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
SUMIFとCOUNTIFを合わせたよう...
-
エクセルでの複数条件下での標...
-
ある一定時間を超えた場合の超...
-
エクセルで年月の合計の関数を...
-
【スプレッドシート】指定の日...
-
EXCEL 経過年数の平均を求めた...
-
エクセルで日付を数字+アルフ...
-
Excelにいついて質問です。
-
エクセルにて「週」から日付を...
-
Excelの表以外が暗い?
-
エクセルで「ぶら下げ」書式を...
-
エクセルで角度の計算できますか?
-
隣のセルに入力したら自動的に...
-
エクセルで、一つのセルに二つ...
-
EXCEL 年月表示をするVBAを教...
-
excelでの文字を隠す方法
-
エクセル2003 MONTH関数で
-
Excel:月またぎを含む日数の差...
-
SUMIF関数の結果が0になってし...
-
エクセル シフト表 6連続勤...
おすすめ情報