役立つ! 総務マガジン

月の大小を気にせずにExcelで末日を表示

「西向く侍」と思い出すより速い! 毎月の末日を関数の組み合わせで求める

請求書などの日付に末日を記載するときには、31日ある「大の月」と、30日以下の「小の月」を意識する必要があります。小の月を「西向く侍」(2、4、6、9、11)と覚えている人は多いと思いますが、Excel関数を使えば、確実かつ自動的に表示できるようになります。

[2018年 7月23日公開]

「どうして月ごとに末日が違うの!?」というイライラからの解放

「翌月末日って30日? 31日?」「しまった、今年の2月は29日まであるのか!」と、カレンダーを見ながらイライラした経験、ありませんか? 大の月・小の月なら暗記できますが、うるう年まで暗記している人は少ないですよね。

しかし、パソコンに組み込まれた膨大な日付データを取り出せるExcelなら、月の大小もうるう年も気にせず、関数を使って当月、翌月、さらにその先の月であっても、末日を確実に表示できるのです。今回は、その方法を見ていきましょう。

「年」「月」「日」をシリアル値に変換する「DATE」関数を活用

例えば、今日が2018年4月14日だとした場合、翌月を求めるには月を表す「4」に「1」を足せばよいと考えられます。しかし、第2回の「Excel時間集計あるある 表示形式を極める」で解説したように、Excelでは1990年1月1日を「1」として、以降1日ごとに「1」ずつ増えていくシリアル値によって日付を管理しています。2018年4月14日のシリアル値は「43204」であり、どれが月を表しているのか、そのままでは分かりません。

そこで便利なのが、[年][月][日]の三つの引数を設定するとシリアル値に変換できる「DATE」(デート)関数です。引数では足し算や引き算も使えるため、[月]の引数で「+1」をすれば、翌月の日付を求められるというわけです。

ここでは今日の日付から自動的に当月や翌月の末日を求めるため、シリアル値から「年」の数字だけを取り出す「YEAR」(イヤー)関数、「月」の数字だけを取り出す「MONTH」(マンス)関数も組み合わせます。以下の手順では今日の日付をベースに、まず翌月1日を求めて、そのシリアル値から「1」を引くことで当月末日を求めていきます。

DATE関数の構文
DATE (年, 月, 日)
YEAR関数の構文
YEAR(シリアル値)
MONTH関数の構文
MONTH(シリアル値)

(1)今日の日付を入力する

DATE関数で取り出す末日のベースとなる今日の日付を、印刷範囲の外(ここではセルI4)に入力します。手入力でも構いませんが、ショートカットキー[Ctrl]+[;]を使えば、今日の日付を確認する手間が省けます。

(2)DATE関数で翌月1日を求める

DATE関数を使って翌月1日を求めます。上図では[fx](関数の挿入)ボタンを使ってDATE関数を設定しています。[年]の引数は、YEAR関数を使って今日の日付から取り出しています。[月]の引数も同様ですが、翌月を取り出すためMONTH関数に「+1」しています。[日]の引数には「1」を設定し、必ず1日を求めるようにしました。セル内または数式バーに直接数式(ここでは「=DATE(YEAR(I4),MONTH(I4)+1,1)」を入力しても構いません。

翌月1日が求められました。

(3)当月末日を計算する

続けて、当月末日を計算します。手順2で求めた翌月1日から1を引くことで求められます。数式バー内をクリックし、数式の最後に「-1」を追加します。

当月末日が求められました。4月は小の月ですが、正しい末日が表示されています。

あり得ない日付を正しく処理する機能を使えば、さらに簡単に

前述の手順と同じように翌月末日を表示するには、DATE関数の[月]の引数に「+2」をして翌々月1日を求め、数式の最後で「-1」すればOKです。しかし、DATE関数では、実は[日]の引数に「0」を設定することで、末日を求めることもできます。

DATE関数は日付としてあり得ない数値を引数に設定しても、正しく処理する機能を持っています。例えば[日]の引数が「0」=「4月0日」というあり得ない日付に設定しても、「4月1日」の前日、つまり前月末日である「3月31日」として正しく処理します。

同様に、[月]の引数が「15」=「2018年15月」と設定しても、それはあり得ないため、「2019年3月」と処理します。さらに例を挙げましょう。

  • =DATE(2018,14,5) → 2019年2月5日
  • =DATE(2018,8,-1) → 2018年7月30日
  • =DATE(2018,-5,35) → 2017年8月4日

以下の手順では、DATE関数の[日]の引数に「0」を設定する方法で、翌月末日を求めていきます。

(1)翌月末日を計算する

DATE関数を使って翌月末日を求めます。上図では[fx](関数の挿入)ボタンを使ってDATE関数を設定しています。[年]の引数には今年を、[月]の引数には翌々月を設定したうえで[日]の引数に「0」を入力し、翌々月の前月末日、つまり翌月末日を求めるわけです。

(2)翌月末日を計算できた

翌月末日が表示されました。

数式の計算結果には「表示形式」で文字を追加する

DATE関数を使って、当月末日や翌月末日を求める方法が分かりました。これが何の日付なのかを明確にするため、日付の前に「請求日:」と表示したいと思います。しかし、数式の先頭に文字を入力するわけにもいきません。

そこで[セルの書式設定]ダイアログボックスから[表示形式]を設定し、DATE関数の計算結果の前に「請求日:」が表示されるようにしましょう。

(1)ユーザー定義の表示形式を設定する

「請求日」欄が表示されているセルE4をアクティブにし、[セルの書式設定]から[表示形式]タブを開きます。[ユーザー定義]を選択したら、「"請求日: "yyyy/mm/dd」(ダブルクォーテーション「"」と「yyyy/mm/dd」は半角)と入力します。なお、「yyyy」は西暦年を四桁で、「mm」は月を二桁で、「dd」は日を二桁で表示します。

(2)日付の前に文字を追加できた

日付の前に「請求日: 」が追加されました。また、「月」の表示が「4」から「04」と二桁になっています。

日付関数を自由自在に扱えれば業務効率がアップする

今回はDATE関数を使って、月によって異なる末日を正確に求める方法を解説しました。「0日」を前月末日、「15月」を翌年3月とするなど、あり得ない日付も正しく処理してくれる頼もしい関数であることも、理解していただけたのではないかと思います。

日付関数を自由自在に扱えれば、総務の業務におけるストレスを大きく解消できるでしょう。ぜひ、実際のExcel文書でも活用してみてください。

協力メディア

できるネット(https://dekiru.net)

もっと関数のスキルを磨きたい! という人はぜひ大塚商会のExcel教室へ

大塚商会では、企業の人材育成をトータルにサポートする「人材育成支援サービス」をご提供しています。ITやCAD、ビジネススキルなどさまざまなジャンルをご用意しており、もちろんExcelを学ぶコースも充実しています。

Excelを基礎から応用まで体系的に学べる「標準コース」や、関数やマクロなど目的に応じて学べる「目的別コース」をご用意しています。大塚商会の研修施設で定期開催されているオープンコースのほか、貴社の業務に合わせてカスタマイズした企業研修(ご来場または出張)も可能です。

Excel研修の詳細情報を見る

Excelをもっと便利に活用するにはOffice 365をおすすめします

マイクロソフトの「Office 365」は、ExcelやPowerPointをはじめとしたOffice製品、メール、スケジュール管理などをクラウド上で使用できます。非常に利便性の高いシステムを実現することが可能です。

Office 365 について見る