いまさら聞けないExcelの使い方講座

【Excel】日付の計算をマスターしよう！エクセルで〇年後の前日を自動計算させる方法

トップスタジオ

2019年3月29日 06:55

「指定した日付から〇年後の前日」はどのように求めればいいの？

　普段のExcel業務の中で、「3年契約しているサービスの有効期間が終了する日付を求めたい」というように、日付データを扱う場面って意外と多いですよね。

　例えば、次の「顧客・契約管理表」では、顧客が契約しているサービスの有効期限を管理しているとします。D列の「契約日」欄（①）には顧客がサービスを契約した日付が入力されており、E列の「契約年数」欄（②）には契約期間（年数）が入力されています。これらのデータをもとに、F列に「契約終了日」（③）を求めるにはどうしたらいいでしょうか。なおこの例では、契約日から「契約年数」が経過した日付の前日までがサービスの有効期間とします。

　「『契約日から〇年後の日付の前日』なんて、簡単に求められる」と思う読者は多いでしょう。確かに暗算で直感的に求められる計算ではありますが、大量のデータが入力された表を管理しなければならない場合は、数式で自動的に計算できるようにしておくととても便利なので、ぜひ覚えてくださいね。

DATE関数、YEAR関数、MONTH関数、DAY関数を組み合わせる

　冒頭の例で紹介した「顧客・契約管理表」で「契約終了日」を計算する場合は、DATE関数、YEAR関数、MONTH関数、DAY関数を組み合わせて行います。使用する関数についてここで少し解説します。

　まずは、DATE関数です。DATE関数は、引数に年、月、日を指定するとシリアル値（Excelが日付データを扱う時に使う値）に変換してくれる関数で、次の書式で記述します。例えば、「2019年3月29日」をシリアル値で表したい場合は、「=DATE(2019, 3, 29)」と入力します（求められたシリアル値には自動的に「短い日付形式」の表示形式が設定されて、「2019/3/29」のように表示されます）。

　今回はさらに、YEAR関数、MONTH関数、DAY関数という3つの関数を使います。YEAR関数は、引数として指定されたシリアル値（日付データ）から「年」を取り出す関数で、次の書式で記述します（同様に、MONTH関数は「月」を、DAY関数は「日」を取り出す関数です）。例えば、YEAR関数の引数に「2019/3/29」を表すシリアル値を入力すると、「2019」という値が得られます。MONTH関数とDAY関数も使い方は同じです。

　ここまでで、それぞれの関数の使い方をわかってもらえたでしょうか。次はこれらの関数をどのように組み合わせていくかを考えていきます。

　例えば「2019年3月29日の3年後の日付の前日」を求める場合は、「年」の部分に「3」を足して、「日」の部分から「1」を引けばよいと考えることができます。

　これを踏まえて、DATE関数を組み立てていきます。DATE関数の1つ目の引数には、「YEAR関数で取り出した『年』＋契約年数」（①）を指定します。DATE関数の2つ目の引数には、「MONTH関数で取り出した『月』」（②）を指定し、3つ目の引数には、「DAY関数で取り出した『日』－1」（③）を指定します。

　数式の組み立て方をわかってもらえたでしょうか。次の項では、実際に表に数式を入力していきましょう。

「〇年後の日付の前日」を求める数式を入力する

　では実際に、「顧客・契約管理表」の「契約終了日」を求めていきましょう。前項の解説を参考にして、セルF4に「=DATE(YEAR(D4)+E4,MONTH(D4),DAY(D4)-1)」（①）という数式を入力します。ここで1つずつ引数の意味を確認していきましょう。1つ目の引数（②）では、「セルD4から取り出した『年』に契約年数（セルE4）を足した値」を指定しています。2つ目の引数（③）では、「セルD4から取り出した『月』」を、3つ目の引数（④）では「セルD4から取り出した『日』－1」を指定しています。

　［Enter］キーを押すと数式が確定され、値が算出されます（⑤）。セルF4に、2019年3月29日から3年後の日付の前日が入力されたことを確認しましょう。

　この数式をセルF10までコピーします。セルF4を選択し、セルの右下隅にマウスポインターを乗せるとマウスポインターの形が変わるので（⑥）、そのままセルF10までドラッグします（⑦）。

　F列の「契約終了日」をすべて求められましたね（⑧）。

うるう年はどうなるの？

　うるう年の2月29日が契約日の場合、どのように計算されるのか気になる読者もいるでしょう。うるう年である2012年の2月29日が契約日で、契約年数が1年の場合を考えてみると、契約終了日は「2013年2月28日」になります（①）。2012年2月29日の1年後は2013年3月1日なので、その前日の2013年2月28日が契約終了日になります。