Forum Discussion
Populating Calendar from an array
I would like to create a monthly calendar view showing milestones for projects based on an array of dates. Can someone suggest the code needed for some pre-fab calendar that will make this simple? Sample data for the calendar would be similar to what is below.
| Task 1 | Task 2 | Task 3 | |
| Project A | 8/1/24 | 8/15/24 | 8/30/24 |
| Project B | 8/2/24 | 8/15/24 | 8/29/24 |
| Project C | 8/3/24 | 8/16/24 | 8/30/24 |
Output on the calendar would be the Project Name next to the Task name.
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.
