Forum Discussion
DaeyunPablo
Aug 08, 2024Brass Contributor
Dynamic Calendar in Excel 365
Hi, I create a dynamic array formula for projects at work. It takes project start & end dates and returns a calendar where its duration is based off of those two inputs. Please let me know if the...
PeterBartholomew1
May 26, 2025Silver Contributor
I tried part of the problem as a challenge; namely creating a blank calendar. My approach starts with the idea that the 'natural' way for Excel to store references to an array is as a thunk. I set out first to create a single month entry
CALENDARMONTHλ
= LAMBDA(SoM,
LET(
calendarMonth, 1+SEQUENCE(6,7,FLOOR.MATH(SoM, 7)),
mm, MONTH(SoM),
VSTACK(
EXPAND(TEXT(SoM, {"mmm","yyyy"}),,7,""),
{"Sun","Mon","Tue","Wed","Thu","Fri","Sat"},
IF(MONTH(calendarMonth)=mm, calendarMonth, "")
)
)
);
Then the worksheet formula is
= LET(
ym, DATE(yyyy, SEQUENCE(8,3,mm), 1),
MAPλ(ym, CALENDARMONTHλ)
)
That rather begs the question of MAPλ.
I had ideas to portion the entries out as monthly lists represented by an array of thunks, but didn't get around to that.