「月末日」や「翌月10日」を求めるには

投稿日:2017年8月20日 更新日:

[Access 2016/2013/2010/2007]

特定の日付を基準に締日や支払日を算出したいことがあります。ここでは、そんなときに役立つ「月末日」「翌月10日」などの求め方を解説します(図1)。

図1 「月末締め翌月20日払い」の計算例。

スポンサーリンク

DateSerial関数の特徴を理解しよう

「月末日」や「翌月10日」を求めるには、年、月、日の3つの数値から日付を作成するDateSerial関数を使用します(構文1)。まずは、この関数の特徴から見ていきましょう。

構文1 年、月、日の数値から日付を作成する
DateSerial(年, 月, 日)

DateSerial関数は、指定した年、月、日をそのまま日付にできない場合、正しい日付に自動調整してくれる頼もしい関数です。例えば、[月]に「0」を指定した場合は「前年12月」の日付が、[月]に「13」を指定した場合は「翌年1月」の日付が得られるという具合です。

DateSerial(2017, -1, 15) → 2016/11/15(自動調整された)
DateSerial(2017,  0, 15) → 2016/12/15(自動調整された)
DateSerial(2017,  1, 15) → 2017/01/15
DateSerial(2017,  2, 15) → 2017/02/15
:
DateSerial(2017, 12, 15) → 2017/12/15
DateSerial(2017, 13, 15) → 2018/01/15(自動調整された)
DateSerial(2017, 14, 15) → 2018/02/15(自動調整された)

引数[日]も同様に調整されます。例えば、[日]に「0」を指定した場合は「前月末日」の日付が得られます。

DateSerial(2017,  10, -1) → 2017/09/29(自動調整された)
DateSerial(2017,  10,  0) → 2017/09/30(自動調整された)
DateSerial(2017,  10,  1) → 2017/10/01
DateSerial(2017,  10,  2) → 2017/10/02
:
DateSerial(2017, 10, 31) → 2017/10/31
DateSerial(2017, 10, 32) → 2017/11/01(自動調整された)
DateSerial(2017, 10, 33) → 2018/11/02(自動調整された)

つまり、何らかの日付を基準にその月の「当月末日」を得るには、「基準日の次の月の0日」を求めればいいわけです。具体的には、DateSerial関数の引数[年]に「基準日の年」、引数[月]に「基準日の月+1」、引数[日]に「0」を指定します。引数[月]の指定を変更すれば、基準日をもとにさまざまな月末日を求めることができます。

DateSerial(基準日の年, 基準日の月-1, 0) → 基準日の前々月末日
DateSerial(基準日の年, 基準日の月, 0) → 基準日の前月末日
DateSerial(基準日の年, 基準日の月+1, 0) → 基準日の当月末日
DateSerial(基準日の年, 基準日の月+2, 0) → 基準日の翌月末日
DateSerial(基準日の年, 基準日の月+3, 0) → 基準日の翌々月末日

なお、基準日の日付を「年」「月」「日」に分解するには、それぞれYear関数(構文2)、Month関数(構文3)、Day関数(構文4)を使用します。併せて覚えておきましょう。

構文2 日時から「年」を取り出す
Year(日時)

構文3 日時から「月」を取り出す
Month(日時)

構文4 日時から「日」を取り出す
Day(日時)

ケースA.「月末締め翌月末払い」の日付を求める

[仕入日]フィールドの日付を基準として、「月末締め翌月末払い」の日付を求めてみましょう(図2図3)。締日は「当月末日」なので、式1のようにDateSerial関数の引数[月]に「仕入日の月+1」を指定します。支払日は「翌月末日」なので、式2のように引数[月]に「仕入日の月+2」を指定します。

式1 [仕入日]の当月末日を求める
締日: DateSerial(Year([仕入日]),Month([仕入日])+1,0)

式2 [仕入日]の翌月末日を求める
支払日: DateSerial(Year([仕入日]),Month([仕入日])+2,0)

図2 クエリの[フィールド]欄に締日として式1、支払日として式2を入力する。

図3 「月末締め翌月末払い」の締日と支払日が求められた。

ケースB.「月末締め翌月20日払い」の日付を求める

次に、「月末締め翌月20日払い」の日付を求めてみましょう。締日は「ケースA」の場合と同様に求めます。支払日は「翌月20日」なので、式3のように引数[月]に「仕入日の月+1」を指定し、[日]に「20」を指定します(図4)。

式3 [仕入日]の翌月20日を求める
支払日: DateSerial(Year([仕入日]),Month([仕入日])+1,20)

図4 締日として式1、支払日として式3を設定。「月末締め翌月20日払い」の締日と支払日が求められた。

ケースC.「15日締め翌月10日払い」の日付を求める

最後に、「15日締め翌月20日払い」の日付を求めます。この場合、仕入日が15日以前かどうかで場合分けをする必要があります。15日以前(15日を含む)の場合の締日は「当月15日」、支払日は「翌月10日」で、15日より後の場合の締日は「翌月15日」、支払日は「翌々月10日」となります(図5)。

図5 仕入日が15日以前かどうかで場合分けする。

場合分けを行うには、IIf関数を使用します(構文5)。引数[式]に指定した条件が成立する場合は[真の場合]、成立しない場合は[偽の場合]を返す関数です。

構文5 式の真偽に応じて値を切り替える
IIf(式, 真の場合, 偽の場合)

実際に、式を考えてみましょう。ポイントは、仕入日の「日」が15以下かどうかをIIf関数の条件式として、DateSerial関数の引数[月]に指定する「仕入日の月+〇」の「〇」の部分の数値を決めることです。式4の締日では、条件が成立する場合は「0」、成立しない場合は「1」とします。式5の支払日では、条件が成立する場合は「1」、成立しない場合は「2」とします。

式4 「15日締め」の締日を求める
締日: DateSerial(Year([仕入日]),Month([仕入日])+IIf(Day([仕入日])<=15,0,1),15)

式5 「翌月10日」の支払日を求める
支払日: DateSerial(Year([仕入日]),Month([仕入日])+IIf(Day([仕入日])<=15,1,2),10)

式4式5の式を当てはめたものが、図6のクエリです。例えば、仕入日が「2017/11/20」の場合、15日以降の日付なので、締日が翌月の「2017/12/15」、支払日が翌々月の「2018/1/10」と正しく求められています。

図6 締日として式4、支払日として式5を設定。「15日締め翌月10日払い」の締日と支払日が求められた。

スポンサーリンク

関連記事

Copyright© Officeのチカラ , 2018 All Rights Reserved.