HomeGoogle DocsSpreadsheet

Find Next Biannual, Annual, Biennial, and Triennial Dates in Google Sheets

Spreadsheet

Published on

Prashanth KV
By Prashanth KV
Modified date:

This tutorial will help you find the next biannual, annual, biennial, and triennial dates in Google Sheets.

Sometimes you may want to find upcoming event dates for finding your next wedding anniversary, ending a hire purchase agreement, or renewing your subscription plan.

Open a Google Sheets and enter your start date or dates as a list in a column.

The dates can be past dates, current dates, or future dates. My formulas will correctly return the upcoming anniversary or event dates.

Here is one example.

Today’s Date – 30/04/2022

Date to Evaluate – 18/03/2019

The next biannual, annual, biennial, and triennial dates will be 18-09-2022, 18-03-2023, 18-03-2023, and 18-03-2025, respectively.

Next Biannual, Annual, Biennial, and Triennial Dates Formula

Next Biannual Date Formula In Google Sheets

As per my example, the dates to evaluate for the upcoming biannual, annual, biennial, and triennial dates are in A2:A.

The outputs in columns C, E, G, and I are evaluated based on today’s date (30-04-2022.)

To simplify the formula, I will use helper columns. Please see columns B, D, F, and H below.

We can write the formula without those helper columns. But that may make it a little difficult to digest.

Let’s see how to find the next biannual, annual, biennial, and triennial dates in Google Sheets.

Number of Months for EDATE function - Helper Formulas

As a side note, if you have one date in A2 or a list of dates in A2:A, there is no need to change my formulas below since all of them are array formulas.

How to Find Next Biannul Date in Google Sheets?

In cell B2, insert the following formula. Other than the IFERROR and ArrayFormula, it contains a few date functions.

Helper Formula # 1 (B2):

=ArrayFormula(
     iferror(
        (year(today())-year(datevalue(A2:A)))*12,
        " "
     )
)

It will return the number of months to conditionally add to the dates in A2:A to get the next biannual dates in C2:C.

The formula first finds the number of years by deducting the current year from the years of dates in A2:A. Then multiply it by 12 months.

In C2, insert the following formula to find the next biannual dates in Google Sheets.

Formula # 1 (C2):

=ArrayFormula(
     iferror(
        if(
           B2:B<=0,edate(A2:A,6),
           if(
              edate(A2:A,B2:B)<today(),edate(A2:A,B2:B+6),edate(A2:A,B2:B)
           )
        )
     )
)

Note:- Select C2:C, then apply Format > Number Date. It applies to columns E, G, and I.

Formula Explanation

The formula adds the number of months in B2:B with A2:A based on the following IF logical tests.

Syntax: if(logical_expression, value_if_true, value_if_false)

Logical Test # 1 – if(B2:B<=0,edate(A2:A,6),

TRUE Part:

If the logical test evaluates to TRUE, the corresponding date in column A is a future date.

If so, it simply adds six months to the corresponding date in A2:A.

FALSE Part:

If the logical test evaluates to FALSE, it executes the value_if_false, which contains another IF (logical test 2).

Logical Test # 2 – if(edate(A2:A,B2:B)<today(),edate(A2:A,B2:B+6),edate(A2:A,B2:B))

It also adds the number of months in B2:B with A2:A dates.

After adding, if the dates returned are past dates, or we can say less than today’s date, the formula adds six months to A2:A since we want to find the next biannual date.

That’s all about it. The remaining are annual, biennial, and triennial dates. Let’s go to that.

Next Annual Date Formula In Google Sheets

You may often want to find upcoming birth anniversary dates of your customers for sending greetings to them in advance.

Here is how to find the dates from a list in Google Sheets.

Note:- We can use the above two formulas as the base to write the other formulas. So first, learn them thoroughly.

How to Find Next Annual Date in Google Sheets?

Helper Formula # 2 (D2):

In cell D2, use the same above helper formula # 1.

Formula # 2 (E2):

=ArrayFormula(
     iferror(
        if(
           D2:D<=0,edate(A2:A,12),
           if(
              edate(A2:A,D2:D)<today(),edate(A2:A,D2:D+12),edate(A2:A,D2:D)
           )
        )
     )
)

In this, the changes compared to formula # 1 are as follows.

  • B2:B becomes D2:D.
  • 6 (months) becomes 12 (months).

The formula adds the number of months in D2:D with the dates in A2:A.

To understand the logical tests, please check the formula # 1 above.

The balance left in finding the next biannual, annual, biennial, and triennial dates are the last two, i.e., biennial, and triennial dates.

Next Biennial Date Formula In Google Sheets

If any event occurs every two years and you want to find the upcoming event date, you can use my below biennial date formula in Google Sheets.

How to Find the Next Biennial Date in Google Sheets?

Helper Formula # 3 (F2):

=ArrayFormula(
     iferror(
        roundup((year(today())-year(datevalue(A2:A)))/2)*24,
        " "
     )
)

This time there are two modifications in the B2 formula.

What are they?

  1. We divided the number of years by two, then rounded them up using the ROUNDUP function.
  2. Then we multiplied the result by 24.

Earlier it was just the number of years * 12.

What is the logic of doing so?

For example, see the year of hire date in cell A2, which is 2019.

The current year is 2022.

The difference is three years.

If we add four (not three) years with the date in A2, we will get the next biannual date.

So what we have done is 3/2 (total years/2) rounded up and multiplied by 24 instead of 12.

So we get 48 (4 years) in F2 instead of 36 (3 years).

Formula # 3 (G2):

=ArrayFormula(
     iferror(
        if(
           F2:F<=0,edate(A2:A,24),
           if(
              edate(A2:A,F2:F)<today(),edate(A2:A,F2:F+24),edate(A2:A,F2:F)
           )
        )
     )
)

In this, the changes compared to formula # 1 are as follows.

  • B2:B becomes F2:F.
  • 6 (months) becomes 24 (months).

The formula adds the number of months in F2:F with the dates in A2:A.

To understand the nested IF, please check Formula # 1.

The balance left in finding the next biannual, annual, biennial, and triennial dates is the last one, i.e., triennial dates.

Next Triennial Date Formula In Google Sheets

If an event happens every three years, you can use my following Next Triennial Date formula.

Helper Formula # 4 (H2):

=ArrayFormula(
     iferror(
        roundup((year(today())-year(datevalue(A2:A)))/3)*36,
        " "
     )
)

If you follow the helper formula # 3, you can understand this.

Here is the generic form of the above formula for you.

Generic Formula: roundup((current_year-original_year)/3)*36

Formula # 4 (I2):

=ArrayFormula(
     iferror(
        if(
           H2:H<=0,edate(A2:A,36),
           if(
              edate(A2:A,H2:H)<today(),edate(A2:A,H2:H+36),edate(A2:A,H2:H)
           )
        )
     )
)

In this Next Triennial Date Formula, the changes compared to formula # 1 are as follows.

  • B2:B > H2:H.
  • 6 (months) > 36 (months).

The formula adds the number of months in H2:H with the dates in A2:A.

To understand the logical IF part, please check Formula # 1.

That’s all about how to find the next Biannual, Annual, Biennial, and Triennial dates in Google Sheets.

Thanks for the stay. Enjoy!

Resources

  1. Create a Countdown Timer Using Built-in Functions in Google Sheets.
  2. Find the Past or Future Closest Date to Today in Google Sheets.
  3. Get a Dynamic Date that Advances/Resets in Google Sheets.
  4. Current Quarter and Previous Quarter Calculation in Google Sheets.
  5. Convert Dates To Fiscal Quarters in Google Sheets.
  6. Array Formula to Generate Bimonthly Dates in Google Sheets.
Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.
Excel Formula

Finding Most Frequent Text in Excel with Dynamic Array Formulas

Looking to identify the most frequently occurring text in Excel? You can do this...
Excel Formula

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...
Excel Formula

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...
Excel Formula

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

More like this

Spreadsheet

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...
Spreadsheet

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...
Spreadsheet

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

4 COMMENTS

  1. Thanks, Prashanth, the formula worked wonderfully. I have a few questions in trying to comprehend the formula. If it is an array formula, should B5 be B5:B, D5 be D5:D, and C5 be C5:C? Why is D5/freq1 necessary in this case? What does the sequence do? Thanks again for your advice.

    • Q1: “If it is an array formula, should B5 be B5:B, D5 be D5:D, and C5 be C5:C?”

      Answer: No, the same cell references can be used. However, to turn it into an array formula, you might need to use the MAP function.

      Q2: “Why is D5/freq1 necessary in this case?”

      Answer: This is necessary because it facilitates the initial offset by a year (you can also specify 2 years or 3 years, as needed). It controls that part. The freq2 parameter manages the month part.

      Q3: “What does the sequence do?”

      The SEQUENCE function generates a sequence of dates based on “freq2.”

      Feel free to share a sample sheet. I’ll try to convert it into an array formula for you.

  2. Hi Prashanth,

    Could you please advise on the formula for scenarios where an event occurs either quarterly or monthly, such as dividend payments? Assuming the original date is Jan 1, 2001, or Jan 1, YYYY, I would like the formula to return Jan 1, 2023, as the next payment. Following Jan 1, 2023, the cell should return April 1, 2023 (for quarterly) or Feb 1, 2023 (for monthly). Is this possible?

    Thanks,
    Jack

    • Hi Jack,

      You can try using this formula:

      =ArrayFormula(
      LET(
      dt, B5, freq1, D5, freq2, C5,
      fp, EDATE(dt, freq1*12),
      seq,
      EDATE(DATE(MAX(YEAR(TODAY()), YEAR(dt)), MONTH(dt), DAY(dt)),
      SEQUENCE(ROUNDUP(12/freq2), 1, freq2, freq2)
      ),
      TO_DATE(IF(fp> TODAY(), fp, XLOOKUP(TODAY()+1, seq, seq, , 1, 2)))
      )
      )

      Inputs:

      B5: Starting date
      D5: First annual, biennial, … frequency (e.g., 1 for yearly, 2 for biennial)
      C5: Frequency of calculations (e.g., 1 for monthly, 3 for quarterly)

      I hope this helps.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.