Highlighting dates on a Calendar between start and end date

I have a calendar in excel (I modified one of the existing excel calendar templates) and I have a table of events with Start and End dates. I want to use conditional formatting to highlight the dates in the calendar between the start and end date.

I have no issues when it's a single date, using the match function I am able to highlight all those single dates, but I am running into problems when it's a range of dates.

I found something online with naming each individual date cell and using AND and OR functions to do it, but I ended up having too many events for this to be a viable solution. My conditional format formulas ended up exceeding the space allowed (I couldn't paste the formula in the box once it got too long, assuming I hit some sort of character limit).

I feel like there must be a better solution.

What I have done in the past is to expand the table of events so that you have a record for each day of the date range, and to use this table as the source to fill in the calendar. This can be done automatically using a few formulae. Please attach a sample file showing what you have and I'll see how it can be amended.

Hope this helps.

Pete

Hi Pete,

Here is what I have so far. I have it working well if there is only 20 or of each type of event.
Unfortunately some of the event lists are 100+ in length, so the method I was using doesn't really make sense.

Thanks

I didn't envisage your calendar to be like that, i.e. to highlight just the date. In my calendar files I have generally transferred over the description of the event. There are some examples of calendar files where a date-range (from and to) is used as the source data in this thread:

https://www.excelforum.com/excel-for...-workbook.html

You might find it useful to read each post, but if you want to jump ahead the final file is attached to Post #29. In addition to expanding the date range in the blue-coloured table in the Events sheet, this file also incorporates colour coding to identify particular workers, so you may find this to be a useful alternative way of achieving your goals.

Hope this helps.

Pete