Excel에서 TODAY를 사용하여 특정 개월 수 내에 날짜의 다음 인스턴스를 계산하시겠습니까?

Question 1

다음은 Excel 2010(및 이전 버전)에서 작동하는 이해하기 쉬운 수식 기반의 비배열 솔루션입니다. (숨겨질 수 있는) 도우미 열을 사용합니다.

24개월 미만 및 18개월 초과의 요구 사항이 항상 충족될 수는 없고 엄격한 요구 사항은 24개월 미만이므로 반대편 요구 사항을 18개월 이상으로 완화했습니다.

특정 날짜에 대해 가능한 목표 날짜는 세 가지뿐입니다. 해당 날짜로부터 18개월이 되는 해의 3월 31일 또는 9월 30일, 또는 다음 해 3월 31일입니다. 기준에 맞는 첫 번째 항목을 선택하면 됩니다.

질문은 오늘을 기준으로 한 결과를 지정합니다. 나는 이것이 다른 "오늘"에도 어떻게 작동하는지 보여주고 싶었습니다. 셀 A2에는 =TODAY(). A열의 다른 셀은 설명을 위한 다른 날짜일 뿐입니다. 특히 3월 31일과 9월 30일과 관련된 "국경 날짜"에 관한 것입니다. 수식은 날짜 셀을 참조하지만 대신 TODAY()를 하드 코딩할 수 있습니다.

I:J 열은 단지 설명을 위한 것입니다. 결과 값이 선택된 이유를 이해하는 데 도움이 되도록 날짜 열에 18개월 및 24개월 날짜를 표시합니다.

도우미 열은 C:E입니다. 여기에는 A 열의 날짜에 대한 세 가지 후보 목표 날짜가 포함됩니다. 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)

요구 사항으로 인해 하나의 목표 날짜만 적합합니다. SUMPRODUCT는 일반(배열이 아닌) 수식을 사용하여 배열 비교를 처리합니다.

C2:E2<EDATE(A2,24)A열 날짜로부터 24개월 미만인지 여부를 기준으로 각 대상 날짜에 대해 TRUE/FALSE(1/0)를 반환합니다.

C2:E2>=EDATE(A2,18)마찬가지로 날짜가 A 열 날짜로부터 18개월 이상인지 여부에 따라 각 대상 날짜에 대해 1/0을 반환합니다.

단 하나의 목표 날짜만이 두 조건을 모두 충족하므로 해당 1/0 값의 곱은 1해당 날짜와 0다른 두 날짜에 대한 것입니다. 해당 제품에 각 대상 날짜 셀의 값이 곱해집니다. 날짜는 숫자로 저장되므로 결과는 적격 목표 날짜를 나타내는 숫자입니다. 날짜 형식만 지정하면 됩니다.

Answer

다음은 Excel 2010(및 이전 버전)에서 작동하는 이해하기 쉬운 수식 기반의 비배열 솔루션입니다. (숨겨질 수 있는) 도우미 열을 사용합니다.

24개월 미만 및 18개월 초과의 요구 사항이 항상 충족될 수는 없고 엄격한 요구 사항은 24개월 미만이므로 반대편 요구 사항을 18개월 이상으로 완화했습니다.

특정 날짜에 대해 가능한 목표 날짜는 세 가지뿐입니다. 해당 날짜로부터 18개월이 되는 해의 3월 31일 또는 9월 30일, 또는 다음 해 3월 31일입니다. 기준에 맞는 첫 번째 항목을 선택하면 됩니다.

질문은 오늘을 기준으로 한 결과를 지정합니다. 나는 이것이 다른 "오늘"에도 어떻게 작동하는지 보여주고 싶었습니다. 셀 A2에는 =TODAY(). A열의 다른 셀은 설명을 위한 다른 날짜일 뿐입니다. 특히 3월 31일과 9월 30일과 관련된 "국경 날짜"에 관한 것입니다. 수식은 날짜 셀을 참조하지만 대신 TODAY()를 하드 코딩할 수 있습니다.

I:J 열은 단지 설명을 위한 것입니다. 결과 값이 선택된 이유를 이해하는 데 도움이 되도록 날짜 열에 18개월 및 24개월 날짜를 표시합니다.

도우미 열은 C:E입니다. 여기에는 A 열의 날짜에 대한 세 가지 후보 목표 날짜가 포함됩니다. 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)

요구 사항으로 인해 하나의 목표 날짜만 적합합니다. SUMPRODUCT는 일반(배열이 아닌) 수식을 사용하여 배열 비교를 처리합니다.

C2:E2<EDATE(A2,24)A열 날짜로부터 24개월 미만인지 여부를 기준으로 각 대상 날짜에 대해 TRUE/FALSE(1/0)를 반환합니다.

C2:E2>=EDATE(A2,18)마찬가지로 날짜가 A 열 날짜로부터 18개월 이상인지 여부에 따라 각 대상 날짜에 대해 1/0을 반환합니다.

단 하나의 목표 날짜만이 두 조건을 모두 충족하므로 해당 1/0 값의 곱은 1해당 날짜와 0다른 두 날짜에 대한 것입니다. 해당 제품에 각 대상 날짜 셀의 값이 곱해집니다. 날짜는 숫자로 저장되므로 결과는 적격 목표 날짜를 나타내는 숫자입니다. 날짜 형식만 지정하면 됩니다.

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