Auto Populate a Linear Calendar Based On A List With Date Ranges

Hey EF Community, I would greatly appreciate if you could help me out!

You can see my attached spreadsheet for guidance.

I would prefer this be done using formulas as this environment does not play well with macros and code.

I have one table with three columns -- Names / Start Date / End Date

I already did some of the work. I have formulas set up in an adjacent table so if James Harden is gone from 5/12 to 5/15, it will auto-create lines for James Harden 5/13 and James Harden 5/14. See the attachment for an example.

On another sheet I have a table that acts as a linear calendar. The first column is Names and every column after that is a date.

Here is what I am trying to do -- Let's say a field somewhere on the calendar table lines up horizontally with James Harden and vertically with any of the four dates (5/12, 5/13, 5/14, 5/15), I want that field to be highlighted. It could hold any value as long as it is highlighted. If that field lines up with a name and date that does not fit into any range, then nothing happens.

Bonus Points: If I could have a fourth column in my original table with three options (leave, travel, training) and have the highlight be a different color depending on what that value is. This would be a luxury, not a necessity however!

see attached
using conditional formatting formula

Formula:

Please Login or Register  to view this content.

Re: bonus
add another criteria of countifs

ps your example output has James harden highlighted instead of kobe

not a problem
bear in mind the larger you make the array for countifs...the slower it runs
ie if you put J:J its unbearable to work with to keep it as low as you can to keep the performance higher
also just in case you were unsure, you must make all criteria the same size

that aside enjoy
Cheers
Hum