「Excelで○ヶ月以上前の日付セルを判定したい」と質問されたのでそのときのをまとめてみました。

数値の計算はイメージできても日付の計算は使ったことないとあまりピンとこない人は多いかもしれない。なわけで以下の流れで説明しようかと思います。

• Excelでの日付とは
• ○日以上前の判定
• ○ヶ月以上前の判定

Excelの日付とは

セルに何かを入力すると入力したものが表示されます。入力した内容に応じてExcelは自動的にデータの種別を決めてたりします。「あいう」といか入れれば文字列になるし半角英数で「123」と入れればこれは数値になります。「2019/3/22」や「3/22」と入れれば日付/時刻になります。このとき、入力に応じて表示内容を変えてくれるのもExcelの機能ですがここでは触れません。

Excelにおける日付とはシリアル値という1899/12/31を起点として連続した数値です。Excelの関数の説明でも度々でてきますシリアル値です。Excelのヘルプだとこんな説明。

日付は連続したシリアル値として保存され、日付の計算が行われています。既定では、1899 年 12 月 31 日がシリアル値 1 として保存されているため、39,448 日後の 2008 年 1 月 1 日のシリアル値は 39448 になります。

ExcelのHelpより

「2019/3/22」や「3/22」と入力すると自動的にシリアル値としてセルに入力され、表示をExcelが適宜変えてくれています。シリアル値と判定される場合は自動的に日付の形式になります。¹⁾

「○日以上前」の判定

具体的な日数が合ったほうがわかりやすと思うのでここでは「3日以上前」とします。

上で説明した通り日付データとはシリアル値という連続する数値です。数値なので足し引きができます。例えばA1に2019/3/22、B1に2019/3/20とある状況でC1に=A1-B1と計算させると3が出てきます。これはA1とB1のシリアル値がそれぞれ43546と43544だからです。

知りたいのは「本日から3日以上前」です。今日が2019/3/22としてとあるセルに2019/3/19やそれよりも前の日付かどうかを知りたいのです。本日のシリアル値は関数today()で得られます。today()を使ってA1に入っている日付が本日よりも3日以上前であるかは次の式ででます。

=today()-A1>=3

“today()-A1″で本日との日数差を出し、その日数さが3以上であることを”>=3″で計算しています。ここで出てくるのは真偽値で3以上であればTRUE、3未満であればFALSEとなります。この計算式をセルに表示するでも良いし、条件付き書式「数式を使用して、書式設定するセルを決定」の条件式に当てはめて該当するセルの色を変えるのでも良いかと思います。

「○ヶ月以上前」の判定

ここからが本題です。ここでも具体的な数値が合ったほうが良いので「3ヶ月以上前」としましょう。一応前提として書いておきますが、ここで言う「○ヶ月前」とは月を基準にしています。例えば、2019/3/1であっても2919/2/28は1ヶ月前として扱います。

基本的な計算式「今日の日付-確認したい日付<=確認したい差」は変わりませんが幾つか注意する点があります。

シリアル値は「日が連続した数値」なので日に関する計算ではそのまま数値として使えましたが月の場合そうは行きません。月部分の数値は日付(シリアル値)から月部分の数値を返すmonth()関数で取得できます。2019/3/22なら3が取得できます。

ここで「今日の月を表す数値-確認したい日の付きを表す数値>=3」だとうまくいかない場合はあります。年末年始をまたがった場合です。2018/12と2019/3で3ヶ月以上前と判定したいところですがこのままだと3-12=-9となります。年の情報が抜け落ちているからですね。なので年の数値を取得し計算式に取り込みましょう。

日付(シリアル値)は日が連続した数値でしたが今回は月を連続した数値が必要となります。月は1から始まり12から1を足すと年が1つ増え月は1になります。というわけで年に12をかければ今まで繰り返された月数になるというわけです。日付の年部分はyear()関数で取得できます。A1に2019/3/22が入っているとして、月の連続値を取得する場合はこうなります。¹⁾12進数ってのを考えるとmonth(A1)をmonth(A1)-1としたくなるけれど、1から始まる連続値と考えればそうでもない。

=year(A1)*12+month(A1)

これで0年1月を1から連続した月の値が得られたので後はこれを計算するだけです。3ヶ月以上前を判定する式はこうなります。ここでもA1に確認した日が入っているとします。

=year(today())*12+month(today())-(year(A1)*12+month(A1))>=3

少し長くなりましたが行っていることは3日以上前のときと同じです。year(today())*12+month(today())で今日の月を表す連続値を、year(A1)*12+month(A1)で確認したい日の月を表す連続値を取得しています。それらを引いた値が3以上であれば3ヶ月以上前となります。

長々と説明しましたが、わかってしまえば難しくはないと思います。使った関数はtoday()/month()/year()の3種類だけです。少し躓くとすると、Excelにおける日付とはシリアル値であるということと、月の連続値を考えるところぐらいでしょうか。こういう前例を作った経験があれば今後似たようなものでも大丈夫かと思います。

Footnotes

---

1. 表示形式を変えたい場合は[セルの書式設定]->[表示形式]から変えましょう。 [↩]