Calendar for Scheduling Time Away

AC_Barron 

I have attached an example. If you find you want a different style of calendar (year-at-a-glance, or weeks that start with Monday, etc.), the following technique can be adapted for that. This example assumes that your Excel is Excel 2019 or a more recent version; the main formulas use the function TEXTJOIN.

The technique assumes that the absence data is stored in a specific range of cells or in an Excel table. I created a table named tblAbsences, on the EmpeeData worksheet. You would add/edit/remove rows in this table (they do not have to be in any particular order), and the automatic formula for the Calendar Code column derives the text that will be displayed on the Month View worksheet calendar.

The Month View calendar derives its day numbers from the values you select/type in cells B2/C2. The formulas in all the cells below those day numbers (or where those day numbers could appear in other months) do the same work:

1. Derive an Excel date value from cells B2, C2, and the day number cell just above it.
2. Filter the entries in tblAbsences that apply to this derived date, grabbing just the Calendar Codes.
3. Concatenate the filtered Calendar Codes, separating them with a delimiter (of one or more characters in cell D2; you can change these).

Here is such a formula, specifically for cell B8:

=LET( date, DATEVALUE(B7&"-"&MoMonth&"-"&MoYear),
    matches, FILTER( tblAbsences[Calendar Code], (tblAbsences[Start of Absence]<=date) * (tblAbsences[End of Absence]>=date), ""),
    result, TEXTJOIN($D$2, TRUE, matches),
    IFERROR(result,"") )

I protected the Month View worksheet against accidental changes, so that only cells B2, C2, and D2 are normally editable. But if you want to edit this calendar for some reason (e.g., to increase row heights or change fonts), you can unprotect it by right-clicking the worksheet tab and selecting "Unprotect Sheet…".