If you use Google Sheets and need to find upcoming event dates, such as your next wedding anniversary or subscription plan renewal, then this tutorial is for you!
Introduction
Finding the next biannual, annual, biennial, and triennial dates in Google Sheets can be a daunting task. However, with a few formulas, you can easily navigate through your spreadsheet and find the dates you need.
Find the Next Biannual Date
To find the next biannual date, follow these steps:
- Open Google Sheets and enter your start date or dates as a list in a column.
- Your dates can be past, current, or future dates.
- Use the following formula to find the next biannual dates:
=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)))))
The formula will return the next biannual dates in column C.
Find the Next Annual Date
To find the next annual date, use the same formula as the one for the biannual date, but with a few adjustments:
- Use the following formula as the helper formula in cell D2:
=ArrayFormula(iferror((year(today())-year(datevalue(A2:A)))*12," "))
- Use the following formula to find the next annual dates:
=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)))))
The formula will return the next annual dates in column E.
Find the Next Biennial Date
If you need to find the next biennial date, follow these steps:
- Use the following formula as the helper formula in cell F2:
=ArrayFormula(iferror(roundup((year(today())-year(datevalue(A2:A)))/2)*24," "))
- Use the following formula to find the next biennial dates:
=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)))))
The formula will return the next biennial dates in column G.
Find the Next Triennial Date
To find the next triennial date, follow these steps:
- Use the following formula as the helper formula in cell H2:
=ArrayFormula(iferror(roundup((year(today())-year(datevalue(A2:A)))/3)*36," "))
- Use the following formula to find the next triennial dates:
=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)))))
The formula will return the next triennial dates in column I.
That’s all there is to it! With these formulas, you can easily find the next biannual, annual, biennial, and triennial dates in Google Sheets.
Enjoy exploring the possibilities!
Resources:
- Create a Countdown Timer Using Built-in Functions in Google Sheets
- Find the Past or Future Closest Date to Today in Google Sheets
- Get a Dynamic Date that Advances/Resets in Google Sheets
- Current Quarter and Previous Quarter Calculation in Google Sheets
- Convert Dates To Fiscal Quarters in Google Sheets
- Array Formula to Generate Bimonthly Dates in Google Sheets
