エクセル関数で締日付を得る方法

-- 初心者の方々の参考になれば幸いです --

=IF(B2=31,DATE(YEAR(A2),MONTH(A2)+1,0),DATE(YEAR(A2),MONTH(A2)+IF(DAY(A2)>B2,1,0),B2))
※セルは2行目を想定しています・・・B2,A2

要件
1.VBAは使用せずに関数を使用します
2.月内の締日は1回のみとします
3.月末締と任意日締が存在します

結果として得たい日付

20日締 5/10 → 5/20
5/25 → 6/20
月末締 5/10 → 5/31
6/10 → 6/30

セル情報
 A列:伝票日付
 B列:締日(月末締は31とします)

日付関数の便利な機能を利用します
 エクセルは実際には存在しないような日付でも自動計算します。
 例えば 6月0日は6月1日の前日なので5月31日になります。
 同様に 2012年13月1日は2013年1月1日 ですし、
 2012年14月31日 は 2013年2月31日 で 2013年3月3日になります。
 ※便利ですね!

解説(※セルは2行目を想定しています)
沢山書いてて難しそうですが、読んで頂けば大丈夫だと思います。
プログラム経験者に「いまさら」って言われそうな定番な内容です。

1.月末締と任意日締は分けて考えます。
  月末締は締日(B2)=31ですからIF関数を使用して
  → IF( B2=31 , 月末締の計算 , 任意日締の計算 )
   となります。

2.月末締は伝票月の最終日なのですが、
  大小月や閏年に配慮が必要となり面倒なので、
  伝票月の、翌月の、1日の、前日と考えます。
  1日の前日をエクセル関数では0日と指定できます。
  → 6月0日 = 5月31日 で
  → 3月0日 = 2月28日 で閏年なら2月29日になります。

  DATE関数を使用して月末日を得るには、
  → DATE(伝票日付の年, 伝票月の次月, 0日)
  → DATE(YEAR(伝票日付), MONTH(伝票日付)+1, 0)
  → DATE(YEAR(A2), MONTH(A2)+1, 0)

3.任意日締は伝票日付の日が締日より小さければ伝票月で
  大きければ伝票月の翌月になります。

  締日に応じて翌月にするには次のようにして、
  伝票月に加算する月数を計算します。
  → IF( DAY(伝票日付) > 締日, 1, 0 )

  DATE関数を使用して加算月を含めた式にすると
  → DATE(伝票日付の年, 伝票日付の月 + 加算月, 締日)
  → DATE(YEAR(A2), MONTH(A2) + 加算月, B2)
  → DATE(YEAR(A2), MONTH(A2) + IF( DAY(A2)>B2,1,0), B2)

EOMONTHなど便利な標準関数を使えばもっと簡単になりますが、
この方法なら、
基本関数しか使っていないのでバージョンに縛られませんよ


カテゴリー: Excel パーマリンク