\CALENDAR

In-formula Calendar Template with dynamic formatting

Syntax in Worksheet: =\CALENDAR(dates, tasks, [categories], [first_day_of_week]) => array
Syntax in Conditional Formatting Rule Manager: =\CALENDAR(tag) => boolean

=LAMBDA(dates,[tasks],[categories],[first_day_of_week],
	IF(AND(ISOMITTED(tasks),COUNTA(dates)=1), // Module for use in Conditional Formatting - detect hidden tag
		LET(
			txt, INDIRECT("RC",0),
			ct, UNICHAR(917631),
			tags, IF(RIGHT(txt,1)=ct,
				LET(
					ts, TEXTSPLIT(txt,ct,,1) & ct,
				MAP(ts, LAMBDA(x,
					REDUCE("",SEQUENCE(LEN(x)),LAMBDA(acc,val,
						LET(
							v, RIGHT(LEFT(x,val),1),
							c, IF(v<>"",UNICODE(v),0),
						acc & IF(AND(c>917535,c<917631),UNICHAR(c-917504),"")
						)
					))
				))
				)
			),
		SUM(INT(TEXT(INDEX(dates,1,1),"0")=tags))>0
	),
	LET(	// Regular build-calendar logic
		fdy, first_day_of_week,
		fd, IF(ISOMITTED(fdy),1,fdy),
		rle, UNICHAR(8235),
		pdf, UNICHAR(8236),
		tag, LAMBDA(s,	// Tag function, make ASCII text into hidden unicode tag
			REDUCE("",SEQUENCE(LEN(s)),LAMBDA(acc,val,
				LET(
					c, UNICODE(RIGHT(LEFT(s,val),1)),
				acc  & IF(AND(c>31,c<127),
					UNICHAR(c+917504),
					""
				))
			)) & UNICHAR(917631)
		),
		wds, MAP(SEQUENCE(1,7,fd),LAMBDA(x,TEXT(x,"dddd") & tag("header"))),
		day_t, tag("day"),
		month_t, tag("month"),
		task_t, tag("task"),
		ct, IF(ISOMITTED(categories),
			tasks,
			MAP(tasks,LAMBDA(x,x & tag(XLOOKUP(x,tasks,categories))))
		),
		startd, MIN(dates),
		end, MAX(dates),
		start, DATE(YEAR(startd),MONTH(startd),1),
		date_seq, SEQUENCE(end-start+1,1,start),
		months, FILTER(date_seq,day(date_seq)=1),
	VSTACK(
			wds,
			REDUCE(0,months,LAMBDA(acc, val,
				LET(
					fonm, DATE(YEAR(val),MONTH(val)+1,1),
					pad, WEEKDAY(val,fd)-1,
					week_count, -INT(-(pad+fonm-val)/7), //ROUNDUP with fewer characters
					month_h, TEXTSPLIT(TEXT(val,"mmmm, yyyy^^^^^^"),"^") & month_t,
					weeks,
						REDUCE(0,SEQUENCE(week_count,1,0),LAMBDA(jacc,jval,
							LET(
								week_start, val-pad+7*jval,
								week_h, MAP(SEQUENCE(1,7,week_start),
									LAMBDA(x,
										IF(AND(x>=val,x<fonm),
											" " & rle & DAY(x) & pdf & tag(x),
											""
											) & day_t
									)
								),
								week_tasks, IFERROR(REDUCE(0,SEQUENCE(7,1,0),LAMBDA(kacc,kval,
										LET(
											cd, week_start+kval,
											ctasks, IF(MONTH(val)=MONTH(cd),FILTER(ct,INT(dates)=cd,""),""),
										IF(kval=0,ctasks,HSTACK(kacc,ctasks))
										)
								)),""),
								week, IF(SUM(INT(week_tasks<>"")), VSTACK(week_h,week_tasks & task_t), week_h),
							IF(jval=0,week,VSTACK(jacc,week))
							)
					)),
				IF(val=start,VSTACK(month_h,weeks),VSTACK(acc,month_h,weeks))
				)
			)
		)
	)
)))

Layout elements of the calendar are invisibly tagged. The \CALENDAR function can be entered as a Conditional Formatting formula to apply formatting to tagged elements. There are some standard tags relating to the calendar layout, while others are created from the categories range to format calendar entries.

Documentation

Worksheet usage: Generates a standard 7-column calendar table based on selected data, spanning dates between earliest and latest date in the selected range. If tasks are supplied, calendar fields are filled with tasks for the given date. If categories are supplied, tasks are tagged by category, to be formatted with conditional formatting.

Conditional formatting usage: Returns true if supplied tag is present within a cell. Standard tags applied to calendar fields regardless of the supplied categories are: “header” (for weekday names), “month” (for month labels), “day” (for day labels), “task” (for all tasks), “today” (applied to the day label and tasks on today’s date).

Commentary

This function is basically a dynamic template-in-a-function idea, which I might be a complete flop, but I figure there’s so many people trying to make a calendar in Excel, that it’s bound to get some clicks.

This one is an experimental function showcasing an innovative technique and a new concept. The technique is tagging values with invisible strings made up of zero-width characters from the Tags unicode block. The new concept here is a boolean, self-referential function designed specifically for use in conditional formatting to facilitate layout element recognition and dynamic styling.

The combination of those two opens the door for a new concept of formatted-output lambdas. Styling using the conditional formatting Rules Manager turns out pretty convenient (more so that for example styling pivot tables using the Style Editor) and I’d love to see it catch on.

I’m yet to decide whether combining the main function logic and tag detection functionality for use in Conditional Formatting in a single function is the correct way to go about it. I’m not sure if it adds any overhead to the Conditional Formatting calculations, making the worksheet less responsive. It shouldn’t, as the two functional blocks branch off at the very start of evaluation, but I haven’t performed much testing.

Working with unicode characters, specifically surrogate pairs, i.e. U+D800 and up, is not as straight-forward as with lower unicode ranges. Specifically, the LEN(), MID() and a couple of other text functions do not work properly (check out Compatibility Versions). LEN() returns string lengths which are too high (double-counting the surrogate pair characters) and MID() counts off positions splitting the surrogate pairs. I still use LEN() in such a way that it’s ok for it to return an upper bound on the string length instead of the exact string length, and instead of MID(s,p,1) I use RIGHT(LEFT(s,p),1) – luckily, for some reason, both LEFT() and RIGHT() play nice with surrogate pairs.