Forum Discussion
Populating Calendar from an array
MBuckner1780 I'm sure there's probably many templates out there already, but here's one possible dynamic array formula for MS365:
=LET( first, DATEVALUE(calendarMonth & " " & calendarYear), calendar, SEQUENCE(6, 7, first - (WEEKDAY(first) - 1)), days, IF(MONTH(calendar) = MONTH(first), TEXT(calendar, "dd"), TEXT(calendar, "mm|dd")), table, tblProjectDates[#All], vals, DROP(table, 1, 1), test, vals <> "", dates, TOCOL(IFS(test, vals), 2), keys, TOCOL(IFS(test, TAKE(DROP(table, 1),, 1) & ": " & DROP(TAKE(table, 1),, 1)), 2), MAP(calendar, days, LAMBDA(date,day, TEXTJOIN(CHAR(10),, day, FILTER(keys, dates = date, "")))) )With Wrap Text formatting applied, as well as some Conditional Formatting, the output would look something like this:
Project Calendar
Adjust the row heights and columns widths as needed. See attached...
MBuckner1780 I'm sure there's probably many templates out there already, but here's one possible dynamic array formula for MS365:
=LET(
first, DATEVALUE(calendarMonth & " " & calendarYear),
calendar, SEQUENCE(6, 7, first - (WEEKDAY(first) - 1)),
days, IF(MONTH(calendar) = MONTH(first), TEXT(calendar, "dd"), TEXT(calendar, "mm|dd")),
table, tblProjectDates[#All],
vals, DROP(table, 1, 1),
test, vals <> "",
dates, TOCOL(IFS(test, vals), 2),
keys, TOCOL(IFS(test, TAKE(DROP(table, 1),, 1) & ": " & DROP(TAKE(table, 1),, 1)), 2),
MAP(calendar, days, LAMBDA(date,day, TEXTJOIN(CHAR(10),, day, FILTER(keys, dates = date, ""))))
)
With Wrap Text formatting applied, as well as some Conditional Formatting, the output would look something like this:
Project Calendar
Adjust the row heights and columns widths as needed. See attached...
djclements Thank you for your quick assistance! This is going to give me back several hours of my day.