Creating yearly recurring date sequences in Google Sheets without cell references

Other Questions Google Sheets
1

I’m working on a spreadsheet where I need to generate repeating dates for multiple years automatically. I have specific dates that repeat every year and I want to create a formula that will show these dates for the current year plus future years.

For example, I have these holiday dates:

15-Jan-22
05-Feb-22
18-Mar-22
12-Apr-22
30-Apr-22
15-Sep-22
18-Nov-22
30-Nov-22
10-Dec-22
24-Dec-22

I want a single formula that will automatically generate these same dates for 2022, 2023, 2024 and so on without having to manually update anything each year. The goal is to have the dates hardcoded in the formula rather than pulling from other cells. Is there a way to accomplish this using Google Sheets functions?

2

try using ARRAYFORMULA with the DATE fnctn - =ARRAYFORMULA(DATE(ROW(A1:A10)+2021,{1;2;3;4;4;9;11;11;12;12},{15;5;18;12;30;15;18;30;10;24})) but u gotta tweak row refs. gets messy but works for hardcoding without other cells.

3

I’ve dealt with similar project deadlines and MAKEARRAY works great here. Use this formula: =MAKEARRAY(30,1,LAMBDA(r,c,DATE(2022+INT((r-1)/10),INDEX({1;2;3;4;4;9;11;11;12;12},MOD(r-1,10)+1),INDEX({15;5;18;12;30;15;18;30;10;24},MOD(r-1,10)+1)))) - it generates your whole sequence automatically. INT division figures out which year each row needs, while MOD cycles through your month and day values. Change the first number in MAKEARRAY to control how many rows you want - 30 gets you three years of dates. Best part? It’s completely self-contained with no external dependencies, so it’s maintenance-free for yearly recurring dates.

4

Here’s another trick that’s worked great for me - combine SEQUENCE with DATE to generate multiple years at once. Try =ARRAYFORMULA(DATE(SEQUENCE(3,1,2022),FLATTEN({1;2;3;4;4;9;11;11;12;12}),FLATTEN({15;5;18;12;30;15;18;30;10;24}))) where that first number in SEQUENCE controls how many years you want. This creates all your dates across multiple years in one shot instead of repeating single years. FLATTEN handles the month/day arrays when you’re expanding across years. I’ve found this scales way better for future projections - just change that first SEQUENCE number.