This schedule uses the year and month in cell D1 and K1 to highlight activities like vacation specified in the Excel defined Table. The schedule works like this, enter a date in cell B18 and a formula in cell B20 extracts the names that have an x for the given date.
The names and x are entered manually, however, the activity cells are highlighted automatically based on the start and end date specified in the Excel defined Table.
The date calculations in row 3 are based on the selected year and month, you don't need to change these dates. The activity column AH is based on the name of the activity, start, end date, and the name in the Excel defined Table.
How I built this schedule
Date drop-down list
Cell K1 contains a drop-down list that lets you pick a month, here is how I created it:
Select cell K1.
Click tab "Data" on the ribbon.
Click "Data Validation" button.
Type "Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec" without double quotes in Source:.
Click OK button.
Dates in row 3
These dates are dynamic meaning they change depending on what year and month the user have selected.
We need to create an Excel date and to do that we have the DATE function. It needs three values, year, month, and day. The month value is a number between 1 and 12. 1 is January is 1, February is 2 and March is 3, etc.
Step 1 - Convert month name to the numerical equivalent
Explaining Conditional formatting formula in cell B4
The COUNTIFS function counts the number of rows in the Excel defined Table that meets three conditions. Date =< Start, Date =>End, and if name equal to a name in the table. If all three are met then the formula returns 1 and the cell is highlighted.
Step 1 - Is date in cell B3 larger than or equal to start dates?
criteria_range1 is a cell reference or structured reference to Table1 and column Start. Excel is not happy with structured references in Conditional formatting formulas, however, there is a workaround. Simply use the INDIRECT function and Excel is happy again.
criteria1 is a cell reference to the date in cell B3. The <= is logical operators that make the COUNTIFS function check that the date in B3 is larger or equal to the dates in Table1[Start]. The ampersand character & concatenates the logical operators and the date in cell B3. The double quotes are needed in order to treat the logical operators as text.
Note, the cell references changes to the next cell C$3 when the CF formula moves.
Step 2 - Is date in cell B3 smaller than or equal to end dates?
Here is criteria_range2 and criteria2.
Step 3 - Is name in cell $A4 equal to name in Table1?