エクセルの条件付き書式で期限・締め切りが迫るとセルの色が変わる設定方法 & EDATE関数で正確な2か月後、半年前などの日付を取得する方法

スポンサーリンク

エクセルで、当日から締め切りが近づいてきたらセルの色が変わる、例えば今後1週間や1ヵ月以内の日付だけ色を変更するといったことがエクセルの「条件付き書式」で可能になります。

スポンサーリンク

エクセルの「条件付き書式」で指定した範囲の日付のセルの色を変更する

たとえば、A列にさまざまな日付があります。これらのセルを選択して、今週や今月の日付のみセルの色を変更してみましょう。

さまざまな日付のセルから指定のセルの色を変更する

変更するセルを選択後、「ホーム」「条件付き書式」「日付」をクリックします。

「ホーム」「条件付き書式」「日付」

「次の期間内の日付を含むセルを書式設定」のプルダウンリストから、該当する項目を選択します。

プルダウンリストには「昨日」「今日」「明日」「過去7日間」「先週」「今週」「来週」「先月」「今月」「来月」と10項目しかありませんが、これらで利用が可能であれば簡単にセルの色を変更することができます。

日付の項目は10項目しかない

試しに「今月」を選ぶと「今月(今日を8月1日に設定しています)」を含むセルの色を変更することができました。

「今月」のセル範囲の色を変更

デフォルトの10項目以外で日付を指定する方法

デフォルトの10項目以外、例えば今日から2か月間の日付を指定して、セルの色を変更する場合は少し複雑になります。

先ほどの日付一覧セルを選択後、「条件付き書式」「ルールの管理」をクリックします。

「条件付き書式」「ルールの管理」

先ほど設定した日付ルール「今月」が選択されていますので、「ルールの編集」をクリックします。

条件付き書式ルールの管理

下のようなダイアログボックスが開きますので、「ルールの種類」から「指定の値を含むセルだけを書式設定」、「ルールの内容を編集してください」から「セルの値」を選びます。

書式ルールの編集

「書式ルールの編集」で、「次の値の間」に「=$A$9(今日の日付に設定しています)」と「=$A$9+60」と入力します。これで今日の日付から60日間(2か月)の日付を含むセルのみセルの色を変更できます。入力後、「OK」ボタンをクリックします。

セルの値の範囲を選択

条件付き書式が変更されたことを確認後、「OK」ボタンをクリックします。

条件付き書式ルールの管理

今日(8月1日に設定)から2ヶ月間のセルのみ色が変更されているのが確認できます。

「今日」から2ヶ月間の日付を含むセルのみ色を変更

同様に、「7日以内」なら「+7」を、また例えば2週間前までなら値の範囲に「=$A$9(今日の日付に設定しています)-14」と「=$A$9」を入力すると該当の日付のセルの色を変更することができます。

ところが1ヶ月の日数は月ごとに変わる

先程の表ではうまくいきましたが、ご存じのように月によって1ヵ月の日数はバラバラです。

ですから先程のように「+60」とすると、2月をまたいだ場合は間違えた式になってしまいます。

下の例は、今日を「2月1日」にした例です。特に2月はうるう年もあって、非常に計算がややこしくなります。

2月1日から60日間ですから、4月2日まで2ヵ月後(60日間)としてカウントされてしまいます。

2月から2ヵ月になると単純にはいかない

C列に今日(A9セル)からの日数の経過を表示してみました。

4月2日は60日後なので、今回の書式ルールに含まれています。

1ヵ月の日数は月ごとにまちまち

何か月後または何か月前の日付を取得する「EDATE関数」

実は、そんな月ごとにまちまちの日数であってもちゃんと計算してくれる関数があります。それが「EDATE」関数です。

実は、「A13」セルと「A16」セルにEDATE関数を使用しています。

「A13」 セルには、基準の日(今日)から1ヵ月後の日付を取得するように設定しています。

設定の日から1ヵ月後の日付を取得

また、 「A16」セル には2ヵ月後の日付を取得するように設定しています。

2ヵ月後の日付を取得

実際に、それぞれのセルに入力している数式をB列に表示してみました。

月ごとの日数の計算は単純にはいかない

EDATE関数の使い方

EDATE関数は具体的には、下記のようになります。

=EDATE(基準となるセル,月数)

1ヶ月後であれば月数を「1」、2ヶ月前は「-2」といったように2つ目の引数を設定します。

これで、例えば「=EDATE(A9, 2)」と入力をすると、セル「A9」からちょうど2ヶ月後の同じ日が表示されます。「A9(基準となる日)」を「2月1日」とするとEDATE関数の結果では「4月1日」が表示されます。

試しに、先ほどの「書式ルールの編集」でEDATE関数を利用してみましょう。

「次の値」の右側を「=EDATE($A$9,2)-1」とします。基準となるセル($A$9)の「2月1日」から2か月後、4月1日からマイナス1していますので、「3月31日」の間の数値が書式設定によってセルの色が変更されます。

EDATE関数を利用して2ヶ月後の日付を計算

いかがでしょうか。1ヶ月の日数に関わらず2ヶ月以内の日数のセルに色を付けることができました。

2ヶ月以内のセルの色を変更

エクセルの日付の計算はなかなか難しい

エクセルでは日付データをシリアル値(1900年1月1日を1として、そこからの経過日数)で管理しています。

エクセルの条件付き書式を使って令和元年を表示する方法(ついでに平成元年も可) Excel 365・2019・2016・2010・2007対応
先日、セルの書式設定で「令和1年」表示を「令和元年」表示にする方法をご紹介しました。今回は、条件付き書式で「令和元年」、ついでに「平成元年」の表記を同時にする方法の紹介です。条件付き書式の条件の数は、エクセル2007より無制限にな...

シリアル値と日付のやり取りは簡単にできますが、今回のような月数をまたがる計算をする場合には「EDATE関数」は非常に役に立ちます。

エクセル(Excel)のセルの書式設定で条件を分岐させる! エクセルで「令和元年」表示も可能 Excel 365・2019・2016・2010・2007対応
Windowsのアップデートで「令和」表示も可能になりましたね。ただ「令和元年」ではなく「令和1年」となっていますが。またアップデートされるのでしょうが、今回はセルの書式設定で条件を分岐させ、表示を変える方法です。その機能を利用して、「令...
スポンサーリンク
Office・Windows
スポンサーリンク
リーダーの独り言

コメント

タイトルとURLをコピーしました