Excelで請求書や見積書などを作成する場合、発行日を元に当月末日や翌月末日を求めて表示させたい事は多々あると思います。
例えば下図の場合、【E3】に入力された発行日を元に【E18】に当月末の日付を自動的に表示させています。
(画像をクリックすると大きく表示されます)
当月末日を求める
この場合、【E18】のセルに入力される数式は以下の通りです。

=DATE(YEAR(E3),MONTH(E3)+1,1)-1

関数の意味がさっぱりわからないよ、という方は当月末日を表示させたいセルに数式をコピー&貼り付けをしていただいて、数式内の【E3】を元になる日付の入るセル名に置き換えていただければそのままご利用いただけます。
以下解説です。
まず、最初にあるDATE関数について。
DATE関数は以下の引数を入力することで、日付のデータ(正式には「シリアル値」といいます)を返してくれる関数です。

   =DATE(年の値,月の値,日の値)
   例)  =DATE(2012,2,25) →  2012/2/25

今回はこのDATE関数の中にさらに関数を組み合わせて表現をしています。
まず、年の値を求める一つ目の引数ですが以下が入力されています。

YEAR(E3)

これはセル【E4】に入力された「2012/2/27」というシリアル値(見え方は「平成24年…」となっていますが、実際に入力されているのは前記のシリアル値です)から年に相当する値だけをとりだしてあげるための関数です。
これが一つ目の引数として入力されているので、今回のシート上では「2012」という値が取り出されてDATE関数の一つ目の引数として入力されたことになります。
ここは割りとシンプルですね。
ポイントは2番目&3番目の引数です。
2番目、月の値を入力する引数を見てみしまょう。

MONTH(E3)+1

この数式が意味するものは、E3に入力された月の次の月の値を求めるという事です。
MONTH(E3)だけですと、E3に入力されたと同じ月になりますが、そこに+1することで翌月を求めているのです。
つまり、今回のシートの場合は「3」という値が2つ目の引数に反映されることになります。
そして3つ目の引数。
ここにはただ「1」が入力されています。
この3つの引数をあわせて確認してみると…

=DATE(YEAR(E3),MONTH(E3)+1,1) → DATE(2012,3,1)

ここまでで求められたのが【E3】に入力された日付の翌月1日の日付である事がわかります。
ただ、今回最終的に表示させたいのは、「当月末」の日付ですよね。
なので、今までで求めてきた翌月1日から「-1」をして、前日のシリアル値を求めるのです。

=DATE(YEAR(E3),MONTH(E3)+1,1)-1

「月末」というのは月によって30であったり、31であったり、あるいはうるう年のようなものがあったりで、決まった値にはなりません。ただ、いずれも「1日の前の日」というのは変わりませんのでこういった数式で求められるのです。
また、「12月に1を足すと13月になるのでは…?」と思われる方がいらっしゃるかもしれないですが、DATE関数の2つ目の引数の中で「13」を指定すると、それは翌年1月と認識されるのでこちらも心配は無用です。
では「翌月末日を求めたい」という場合はどうしたらよいでしょうか?
もう皆さんお分かりかと思いますが、まず関数で翌々月1日を求めてそこから1を引けば良いわけです。

=DATE(YEAR(E3),MONTH(E3)+2,1)-1

Excelで日付時刻についてある程度扱ったことのある方には割りと簡単だと思いますが「シリアル値」について理解が少ないと難しい部分になると思います。
まずは上記数式をコピーして自分のシートに貼り付けて使ってみることをお勧めします。
請求書の発行日は変えたものの、締め日を変え忘れて再発行しなければならなくなった、などという間違いはよくありますがこんな工夫一つで防ぐことができます。
ぜひお試しくださいね!


関連情報のご質問がある方は「コメント」へどうぞ!
出来る限りお答えいたします。
(コメントは承認制になっております。反映までにお時間かかりますこと、ご了承ください)
また「役に立ったな!」と思われた方は「拍手」でも良いのでプチッとしていただくととっても励みになってまた頑張って次の記事を書くと思います (^_^;
ぜひよろしくお願いいたします。
ワンズ・ワンHPへ
人気ブログランキングへ