Excelで購入日から年度ごとの経過年数を自動判定して色付けする方法｜DATEDIFより正確な条件付き書式設定

Excelで購入日を入力すると、経過年数や年度ごとの一覧を自動表示したい場合、日付の計算方法によって結果がずれることがあります。特に4月1日から翌年3月31日までを1年度として管理する場合、単純なDATEDIF関数だけでは期待した年度判定にならないケースがあります。

この記事では、購入日を基準にして年度単位で自動判定し、条件付き書式で該当年度を塗りつぶす方法について解説します。4月始まりの管理表や設備管理表、資産管理表などにも応用できます。

DATEDIF関数だけでは年度判定がずれる理由

購入日から何年経過したかを求める場合、DATEDIF関数は便利な関数です。しかし、DATEDIFは「満年数」を計算する関数であり、4月1日を基準とした年度管理にはそのまま利用できません。

例えば、2025年6月1日に購入した商品は、2026年6月1日にならないと満1年として計算されません。そのため、年度単位で見ると2026年度として扱いたい場合でも、Excel上では判定がずれることがあります。

年度管理では「購入日から何年経ったか」ではなく、「各年度開始日時点で何年経過しているか」を判定する考え方が必要になります。

4月1日から翌年3月31日までを1年度として扱う場合、購入日と年度開始日を比較して経過年数を求めます。

例えば、2025年度は2025年4月1日から2026年3月31日までです。そのため、年度表の各列に年度開始日を設定し、その日付時点で購入から何年経過しているかを計算します。

この方法なら、2025年4月1日に購入した場合も、2025年6月1日に購入した場合も、年度基準で正しく色付けできます。

例えば、購入日がG3セル、年度開始日がAI2セルに入力されている場合、以下のような式で判定できます。

=IF($G3=””,””,IF(DATEDIF($G3,AI$2,”Y”)>=0,”1″,””))

ただし、この式だけでは未来の日付まで判定してしまう可能性があります。そのため、年度開始日が購入日より前かどうかを確認する条件を追加すると、より正確になります。

=IF(AND($G3<>””,AI$2>=$G3),”1″,””)

この式では、年度開始日が購入日以降の場合だけ「1」を表示します。その「1」を条件付き書式の条件に設定すれば、対象年度だけ色を付けることができます。

単純な経過年数ではなく、年度基準の経過年数を表示したい場合は、年度の開始日を基準にDATEDIFを使います。

例えば、現在の日付ではなくファイルを開いた時点で経過年度を計算したい場合は、TODAY関数を利用できます。

=IF(G3=””,””,DATEDIF(G3,TODAY(),”Y”))

ただし、この結果は満年数なので、4月始まりの管理では年度判定用の数式と分けて考えることがおすすめです。

年度表のセルを自動で塗りつぶすには、条件付き書式を利用します。

手順は以下の通りです。

例えば、判定用セルにAI3の値を使う場合は、条件付き書式の数式を「=AI3=”1″」のように設定すると、該当年度だけ自動で色を付けられます。

設備や備品などの管理表では、購入日を入力するだけで年度ごとの経過状況が分かる仕組みにすると便利です。

ただし、年度管理では「暦年」と「会計年度」を混同しないことが重要です。1月から12月で管理する場合と、4月から3月で管理する場合では判定方法が変わります。

例えば、2025年6月購入の商品は暦年では2026年まで待つ必要がありますが、4月始まりの年度管理では2026年度として扱うなど、目的に合わせた計算式を設定する必要があります。

Excelで購入日から年度ごとに自動で色付けする場合、DATEDIF関数だけでは4月始まりの年度管理に対応しにくいことがあります。

年度開始日を基準に購入日と比較する方法を使えば、ファイルを開いた日や年度ごとの区切りに合わせて正確に判定できます。

設備管理表や資産管理表などで同じような仕組みを作る場合は、購入日、年度開始日、条件付き書式を組み合わせることで、入力作業を減らしながら見やすい管理表を作成できます。