Forum Discussion

DaeyunPablo's avatar
DaeyunPablo
Brass Contributor
Aug 11, 2024
Unfortunate, I was hoping to see an alternative which can be used in general instead of REDUCE/STACK method... But nice version of yours, looking at it I updated my formula which has the 3rd input 'mth_in_row_num' to expand/shrink the calendar horizontally.

Please do let us know if you come up with a faster alternative later!
djclements's avatar
djclements
Bronze Contributor
May 24, 2025

DaeyunPablo​ 

Better late than never... I revisited this today with improved methods, which can handle a date range spanning hundreds of years efficiently:

=LET(
   wrap_count, 3,
   low, MIN(B7:B15),
   upp, MAX(B7:B15),
   num, CEILING.MATH((YEAR(upp) - YEAR(low)) * 12 + MONTH(upp) - MONTH(low) + 1, wrap_count),
   beg, DATE(YEAR(low), MONTH(low) + SEQUENCE(num,, 0), 1),
   pad, EXPAND("", ROWS(beg), 3, ""),
   lbl, EXPAND(TEXT(beg, "mmm-e"),, 4, ""),
   hdr, IFNA(TEXT(SEQUENCE(, 7), "ddd"), beg),
   bdy, beg - WEEKDAY(beg) + SEQUENCE(, 42),
   arr, HSTACK(pad, lbl, hdr, (MONTH(bdy) = MONTH(beg)) * bdy),
     h, 8, w, 7,
     n, wrap_count * w,
     i, SEQUENCE(COUNTA(arr) / n,, 0),
     j, SEQUENCE(, n, 0),
   INDEX(arr, 1 + QUOTIENT(j, w) + wrap_count * QUOTIENT(i, h), 1 + MOD(j, w) + w * MOD(i, h))
)

Adjust the wrap_count as desired. Cheers!

Resources