【googleスプレッドシート】今日を基準に指定された期限が1年を切ったらハイライトさせる条件付き書式

Question

前提・実現したいこと

googleスプレッドシートを使って、案件ごとに契約期間の一覧表を作成中です。

■現状
B1のセルにTODAY関数で本日の日付を表示させています。
D2:D40のセルに案件ごとの期日（年/月/日）を記入しています。

■実現したいこと
今日の日付（B1セル）からみて、D2:D40の期日が2年を切ったら自動でセルに背景色をつけたいです。

条件付き書式で設定することは分かっていますが、具体的な記述方法が不明なためご教示いただけますと幸いです。

宜しくお願いいたします。

Answer 1

D2:D40　を選択し、
メニュー「表示形式」から「条件付き書式」を選択、
「書式ルール」の「セルの書式設定の条件」リストから
一番下にある「カスタム数式」を選択し
下記の数式を入力。
「書式設定のスタイル」から、条件に当てはまる場合の書式を設定し、
完了ボタンを押します。

=if(and(month($B$1)=2,day($B$1)=29),COUNTIF(D2,"<"&year($B$1)+2&"/3/1"),COUNTIF(D2,"<"&year($B$1)+2&"/"&month($B$1)&"/"&day($B$1)))

---

設定内容の詳細について

数式後半の
COUNTIF(D2,"<"&year($B$1)+2&"/"&month($B$1)&"/"&day($B$1)))
から解説します。

COUNTIFは、第一引数に指定したセル範囲のうち、第二引数の条件にあてはまるセルの数を返す関数です。
たとえば

=COUNTIF(A2:A100,"<10")

とした場合
A2:A100の範囲で、10より小さい数を持つセルの個数が返ってきます。

第二引数には日付条件を付けることができます。
たとえば
=COUNTIF(A2:A100,"<2021/10/21")　とすれば
A2:A100の範囲で、2021年10月21日より前の日付をもつセルの個数が返ってきます。

COUNTIF(D2,"<"&year($B$1)+2&"/"&month($B$1)&"/"&day($B$1)))

の後半「year($B$1)+2&"/"&month($B$1)&"/"&day($B$1))」
は、
セルB1に指定した年月日から、「年」を取り出して+2することで、「B1セルの2年後の日付」を作成しています。

たとえばセルB1が「2021/10/21」ならば
year($B$1)+2&"/"&month($B$1)&"/"&day($B$1))
は「2023/10/21」になります

よってセルB1が「2021/10/21」ならば、条件付き書式に指定したカスタム数式は
=COUNTIF(D2,"<2023/10/21”)
と計算されるため、
D2に指定したセルが2023/10/21より前ならば「１」が返ってきます。
条件付き書式では、計算結果がゼロでなければ条件＝正という意味になるので、
カスタム数式の計算結果で「１」が返ってくる場合、指定した背景色が塗られます。

また、複数の範囲を選択して条件付き書式を設定した場合、数式に指定したセル番地は自動的に
順番がずれてコピーされます。

上記の場合、D2:D40の範囲を選択して条件付き書式を設定したので、
D2セルのカスタム数式には「=COUNTIF(D2,～)」
D3セルのカスタム数式には「=COUNTIF(D3,～)」
D4セルのカスタム数式には「=COUNTIF(D4,～)」
....
...
というように、D40セルまで順番に自動的に設定されることになります。

---

前半の

if(and(month($B$1)=2,day($B$1)=29),COUNTIF(D2,"<"&year($B$1)+2&"/3/1")

の部分は、うるう年を考慮したものです。

B1セルにうるう年の2月29日を指定した場合、
その2年後に29日が存在しないため、正しく計算されません。

したがって、B1セルに2月29日が指定された場合は、「2年後の3月1日より前」という条件を作っています。

Comments

[mst0]

ありがとうございます。
イメージ通りに実現できました。
もしよければ、今後のために今回の設定内容の詳細を教えていただけないでしょうか。

[退会済みユーザー]

追記しました

[mst0]

分かりやすく説明していただきありがとうございます。
上記を応用して、他の資料にも活用させていただきます。
お忙しい中、ご教示いただきありがとうございました。