Excel の TODAY を使用して、特定の月数内の次の日付を計算しますか?

Question 1

これは、Excel 2010 (および以前のバージョン) で機能する、わかりやすい数式ベースの非配列ソリューションです。ヘルパー列 (非表示にできます) を使用します。

24 か月未満と 18 か月を超えるという要件は常に満たされるわけではなく、厳格な要件は 24 か月未満であるため、もう一方の要件を 18 か月以上に緩和しました。

任意の日付に対して、ターゲット日付として選択できる日付は、その日付から 18 か月後の 3 月 31 日または 9 月 30 日、あるいは翌年の 3 月 31 日の 3 つだけです。条件を満たす最初の日付を選択するだけです。

質問では、TODAY に基づいて結果を指定します。これが他の「今日」でどのように動作するかも示したいと思いました。セル A2 にはが含まれています=TODAY()。列 A の他のセルは、説明のための他の日付です。特に、3/31 と 9/30 に関連する「境界日付」のセルです。数式は日付セルを参照しますが、代わりに TODAY() をハードコードすることもできます。

列 I:J は説明のみを目的としています。結果値が選択された理由を理解しやすくするために、列 A の日付から 18 か月および 24 か月後の日付が表示されます。

ヘルパー列は C:E です。これらには、列 A の日付の 3 つの候補ターゲット日付が含まれます。C2 のターゲット 1 には次のものが含まれます。

=DATE(YEAR(EDATE(A2,18)),3,31)

これにより、列 A の日付から 18 か月後の 3/31 という日付が作成されます。D2 のターゲット 2 には次の内容が含まれます。

=DATE(YEAR(EDATE(A2,18)),9,30)

これにより、列 A の日付から 18 か月後の 9/30 という日付が作成されます。E2 のターゲット 3 には次の内容が含まれます。

=DATE(YEAR(EDATE(A2,18))+1,3,31)

これにより、列 A の日付から 18 か月後の年の 3/31 という日付が作成されます。

結果は列 G にあります。G2 の式:

=SUMPRODUCT((C2:E2<EDATE(A2,24))*(C2:E2>=EDATE(A2,18))*C2:E2)

要件により、対象となるターゲット日付は 1 つだけです。SUMPRODUCT は、通常の (配列ではない) 数式を使用して配列の比較を処理します。

C2:E2<EDATE(A2,24)日付が列 A の日付から 24 か月以内であるかどうかに基づいて、各ターゲット日付に対して TRUE/FALSE (1/0) を返します。

C2:E2>=EDATE(A2,18)同様に、日付が列 A の日付から 18 か月以上であるかどうかに基づいて、各ターゲット日付に対して 1/0 を返します。

両方の条件を満たすターゲット日付は 1 つだけなので、1/0 値の積は1その日付と0他の 2 つの日付になります。その積は各ターゲット日付セルの値で乗算されます。日付は数値として保存されるため、結果は条件を満たすターゲット日付を表す数値になります。これを日付として書式設定するだけです。

Answer

これは、Excel 2010 (および以前のバージョン) で機能する、わかりやすい数式ベースの非配列ソリューションです。ヘルパー列 (非表示にできます) を使用します。

24 か月未満と 18 か月を超えるという要件は常に満たされるわけではなく、厳格な要件は 24 か月未満であるため、もう一方の要件を 18 か月以上に緩和しました。

任意の日付に対して、ターゲット日付として選択できる日付は、その日付から 18 か月後の 3 月 31 日または 9 月 30 日、あるいは翌年の 3 月 31 日の 3 つだけです。条件を満たす最初の日付を選択するだけです。

質問では、TODAY に基づいて結果を指定します。これが他の「今日」でどのように動作するかも示したいと思いました。セル A2 にはが含まれています=TODAY()。列 A の他のセルは、説明のための他の日付です。特に、3/31 と 9/30 に関連する「境界日付」のセルです。数式は日付セルを参照しますが、代わりに TODAY() をハードコードすることもできます。

列 I:J は説明のみを目的としています。結果値が選択された理由を理解しやすくするために、列 A の日付から 18 か月および 24 か月後の日付が表示されます。

ヘルパー列は C:E です。これらには、列 A の日付の 3 つの候補ターゲット日付が含まれます。C2 のターゲット 1 には次のものが含まれます。

=DATE(YEAR(EDATE(A2,18)),3,31)

これにより、列 A の日付から 18 か月後の 3/31 という日付が作成されます。D2 のターゲット 2 には次の内容が含まれます。

=DATE(YEAR(EDATE(A2,18)),9,30)

これにより、列 A の日付から 18 か月後の 9/30 という日付が作成されます。E2 のターゲット 3 には次の内容が含まれます。

=DATE(YEAR(EDATE(A2,18))+1,3,31)

これにより、列 A の日付から 18 か月後の年の 3/31 という日付が作成されます。

結果は列 G にあります。G2 の式:

=SUMPRODUCT((C2:E2<EDATE(A2,24))*(C2:E2>=EDATE(A2,18))*C2:E2)

要件により、対象となるターゲット日付は 1 つだけです。SUMPRODUCT は、通常の (配列ではない) 数式を使用して配列の比較を処理します。

C2:E2<EDATE(A2,24)日付が列 A の日付から 24 か月以内であるかどうかに基づいて、各ターゲット日付に対して TRUE/FALSE (1/0) を返します。

C2:E2>=EDATE(A2,18)同様に、日付が列 A の日付から 18 か月以上であるかどうかに基づいて、各ターゲット日付に対して 1/0 を返します。

両方の条件を満たすターゲット日付は 1 つだけなので、1/0 値の積は1その日付と0他の 2 つの日付になります。その積は各ターゲット日付セルの値で乗算されます。日付は数値として保存されるため、結果は条件を満たすターゲット日付を表す数値になります。これを日付として書式設定するだけです。

Question 2

次のユーザー定義関数は、まず今日から 18 か月から 24 か月までのカレンダー範囲を作成します。次に、条件に一致する日付が見つかるまでその範囲をループします。

Public Function ProjDate() As Date
    Dim d1 As Date, d2 As Date, y As Long
    Dim dd As Date, d As Long, m As Long

    d = Day(Date)
    m = Month(Date)
    y = Year(Date)
    d1 = DateSerial(y, m + 18, d + 1)
    d2 = DateSerial(y, m + 24, d - 1)

    For dd = d1 To d2
        d = Day(dd)
        m = Month(dd)
        If (m = 3 And d = 31) Or (m = 9 And d = 30) Then
            ProjDate = dd
            Exit Function
        End If
    Next dd
End Function

ユーザー定義関数（UDF）は非常に簡単にインストールそして次のように使用します:

ALT-F11でVBEウィンドウが開きます
ALT-I ALT-Mで新しいモジュールを開く
内容を貼り付けてVBEウィンドウを閉じます

ワークブックを保存すると、UDF も一緒に保存されます。2003 以降のバージョンの Excel を使用している場合は、ファイルを .xlsx ではなく .xlsm として保存する必要があります。

に取り除くUDF:

上記のようにVBEウィンドウを開きます
コードを消去する
VBEウィンドウを閉じる

に使用Excel からの UDF:

=私の関数(A1)

マクロ全般の詳細については、以下を参照してください。

http://www.mvps.org/dmcritchie/excel/getstarted.htm

そして

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

UDF の詳細については、以下を参照してください。

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

これを機能させるにはマクロを有効にする必要があります。

Answer