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 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.

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.