1

Edit to clarify: the date returned MUST be less than 24 months from the current date because this is an expiration date, and the items must expire at the correct time unless renewed. Another way of putting it is that the date needs to be as close to a 2-year renewal cycle as possible without going over 2 years.

Original post: Good morning! I've been sifting through Google and getting nowhere, so I'm hoping I can get a hand with creating a formula. I need to create a single formula which doesn't reference other cells because I'm hoping to limit the number of errors by other users :)

What I need: find next instance of 03/31 OR 09/30 which is greater than 18 months but less than 24 months from TODAY.

Based on my phrasing above, I feel like some combination of TODAY, OR, <, >, and a month/day identifier would give me what I'm looking for, but I'm having difficulty with the order of operations. Currently my coworkers are using a table (below) to manually calculate these dates and it is tedious.

  • April - Sept Odd = March Next Odd
  • April - Sept Even = March Next Even
  • Oct Even - Mar Odd = Sept Next Even
  • Oct Odd - Mar Even = Sept Next Odd

Thanks in advance for any input, and I'll edit this if I come up with a rough formula after work!

  • You specify both >18 and <24 month requirements. That is not always possible to meet. For example, for March 31, 18 months later (at least by EDATE), falls on 9/30; 24 months later falls on 3/31. Your requirement is that it must be after 9/30 and before 3/31, but there is no target date that meets that requirement. It sounds like <24 is the hard requirement. How should this be handled? Use a date a day earlier (3/30, not a specified target date)? Use a date that is not >18 months (9/30)? – fixer1234 May 24 '19 at 1:15
1

Here's an easy to understand, formula-based, non-array solution that works with Excel 2010 (and older versions). It uses helper columns (which can be hidden).

Since the requirement for <24 months and >18 months cannot always be met, and the hard requirement is <24 months, I loosened the requirement at the other end to >=18 months.

For any given date, there are only three possible target dates: 3/31 or 9/30 in the year 18 months from the date, or 3/31 the following year. You just need to select the first one of those that meets the criteria.

enter image description here

The question specifies the results based on TODAY. I wanted to also show how this behaves on other "todays". Cell A2 contains =TODAY(). The other cells in column A are just some other dates for illustration; especially ones on the "border dates" related to 3/31 and 9/30. The formulas reference the date cell, but TODAY() could be hard coded instead.

Columns I:J are only for illustration. They show the dates 18 and 24 months from the column A date to help in understanding why the result values are selected.

The helper columns are C:E. These contain the three candidate target dates for the date in column A. Target 1 in C2 contains:

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

This creates the date 3/31 in the year 18 months from the column A date. Target 2 in D2 contains:

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

This creates the date 9/30 in the year 18 months from the column A date. Target 3 in E2 contains:

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

This creates the date 3/31 in the year following 18 months from the column A date.

The result is in column G. The formula in G2:

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

Because of the requirements, only one target date will qualify. SUMPRODUCT handles the array comparisons with a normal (non-array) formula.

C2:E2<EDATE(A2,24) returns TRUE/FALSE (1/0) for each target date based on whether the date is less than 24 months from the column A date.

C2:E2>=EDATE(A2,18) similarly returns 1/0 for each target date based on whether the date is >= 18 months from the column A date.

Only one target date will meet both conditions, so the product of those 1/0 values will be 1 for that date and 0 for both other dates. That product gets multiplied by the value in each target date cell. Since dates are stored as numbers, the result is the number representing the qualifying target date. That just needs to be formatted as a date.

0

The following user defined function first creates a calendar span from 18 to 24 months from today. It then loops over the span until it finds a date matching your criteria:

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

enter image description here

User Defined Functions (UDFs) are very easy to install and use:

  1. ALT-F11 brings up the VBE window
  2. ALT-I ALT-M opens a fresh module
  3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the UDF:

  1. bring up the VBE window as above
  2. clear the code out
  3. close the VBE window

To use the UDF from Excel:

=myfunction(A1)

To learn more about macros in general, see:

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

and

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

and for specifics on UDFs, see:

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

Macros must be enabled for this to work!

  • 1
    Seems to return a 0 if today is 3/31/2019 – Ron Rosenfeld May 23 '19 at 17:32
  • @RonRosenfeld You are correct. That is because the span of dates would be from 10/2/2020 through 3/30/2021 and there are no target dates in that span. – Gary's Student May 23 '19 at 18:09
  • @RonRosenfeld The Poster specified less than 24 months rather than less than or equal to . – Gary's Student May 23 '19 at 18:11
  • Good point. But given the inherent ambiguity of measuring months, which can vary in length, I wonder what he really wants when the result is on the margin using particular definitions. – Ron Rosenfeld May 23 '19 at 18:38
  • I appreciate the input! Unfortunately I can't really ask my co-workers to use any macro-enabled spreadsheets; right now, basic data entry in Excel is considered "advanced." But I will clarify in my post why the dates need to be less rather than equal. Thanks!! – MonkeyDLucy May 23 '19 at 21:34
0

Here is a formula. As written, for testing, it references A1. However, you can replace A1 with TODAY() if you find it returns expected results:

=MAX((MONTH(EDATE(A1-DAY(A1)+1,{18;19;20;21;22;23;24}))={4,10})*EDATE(A1-DAY(A1)+1,{18;19;20;21;22;23;24}))-1

The above is an array formula.

Since this is an array formula, you need to "confirm" it by holding down ctrl + shift while hitting enter. If you do this correctly, Excel will place braces {...} around the formula as observed in the formula bar

If you want to avoid the CSE entry procedure, you can try the slightly longer:

=AGGREGATE( 14,4,(MONTH(EDATE(A1-DAY(A1)+1,{18;19;20;21;22;23;24}))={4,10})*EDATE(A1-DAY(A1)+1,{18;19;20;21;22;23;24}),1)-1
  • I'll have to do a bunch of testing with all of these but the first one posted works so far! Ron Rosenfeld I'd love to know what prompted you to remove it? Also Brian I don't think 01/01 creating an error would be a problem since it's a state holiday and I don't expect any state employees (besides myself) would show up :) – MonkeyDLucy May 23 '19 at 21:41
  • @MonkeyDLucy The first trial did not work properly in all cases, and would occasionally return an incorrect future date. – Ron Rosenfeld May 23 '19 at 22:59
  • @Brian With regard to 1/1/2020, what logic are you using to have it return 2022? It returns 9/30/2021 which is 20 months different (i.e. between 18-24) 3/30/2022 is 26 months different (more than 24) – Ron Rosenfeld May 23 '19 at 23:20
  • Oops my mistake, 2021 was correct. Comment deleted. I used =text(your formula,"m/d/yyyy") because I am a data guy and despise formatting. I would love to understand more of how these are working. – Brian May 23 '19 at 23:29
  • @Brian I think if you use the Formula Evaluation tool, and read Excel HELP with regard to the EDATE function. It should become clear. In Excel, Dates are stored as sequential integers with 1 = 1/1/1900. The portion A1-DAY(A1) will return the last day of the preceding month: +1 then returns the first day of the current month. By starting with 1st day, one avoids the issues of measuring month difference when they have different numbers of days. – Ron Rosenfeld May 23 '19 at 23:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.