Highlight dates on calendar if between 2 dates

[vayana]

Hello,

I'm trying to get excel to automatically highlight dates on my calendar that fall between a number of given dates, but this is causing some issues.
First of all excel doesn't support conditional formatting between dates, and second I can't copy conditional formatting rules to apply to another cell easily.
It would be possible to manually set up rules for all 365 days of a year, but I was hoping to use a formula that can do this automatically.

I found a formula that uses median that is quite clever, but I haven't found a way to apply it to a part of the calendar or the entire calendar for that matter.

This formula does a neat job for a single date, but it would be nice if it would work for an entire month.
=IF(F5=MEDIAN(F5,Bookings!D3:Bookings!E3),"Yes","No")

So maybe someone here can help me out with this.

I attached a version for your convenience to get a better idea.

Thanks,

Michael.

[Pepe Le Mokko]

You make things difficult because the month names are placed in merged cells. This makes it difficult to reference the month for the CF

[Ron Coderre]

I can't figure out what you want to happen in the posted workbook.
Can you describe the CF rules you want to employ?

[vayana]

I'm more than happy to explain what I want:

I have rows for each apartment on both sheets, and a calendar for each apartment on the overview sheet (although there's only one in the example I uploaded earlier)
Now, behind the apartments on the bookings sheet, there are columns with check-in and check out dates. (Currently there's only the first columns of dates filled)

e.g. if I have a booking for a room that starts on 1-1-2014 and ends 10-1-2014 on the "bookings" sheet, I would like the calendar for that apartment on the "overview" sheet to highlight the days that it's been booked for.

If I set up a formula for each apartment with its own calendar, I'll have a visual overview of the availability for each apartment on the individual calendars.

I hope that makes more sense, and as an example I'll upload something that looks like what I would like to happen automatically. For your information: I've just used 1 apartment: La Royale Beach Condo unit 601 to show what I would like to do. The bookings on the booking sheet are highlighted manually on the overview sheet for that apartment.

I just put the calendars for the other apartment in to clarify that they all have their own availability calendar.

[vayana]

I found this formula in another excel sheet that has a horizontal calendar that highlights events between 2 dates.
Horizontal meaning:

The months are in column 1 below each other, the days are filled in behind each month horizontally.

Now I'm no good in reading formulas, but could someone tell me if this could be applied to my calendar structure as per uploaded previous example?
The below formula highlights cells in the month January, of which the word January is in merged cell B,C,D 5 and the numbers run from G5 till AK5 (1 -31)

=OR(AND(E5>=Ya,E5<=Yaa,E5<>"",Ya<>"",Yaa<>""),AND(E5>=Yb,E5<=Ybb,E5<>"",Yb<>"",Ybb<>""),AND(E5>=Yc,E5<=Ycc,E5<>"",Yc<>"",Ycc<>""),AND(E5>=Yd,E5<=Ydd,E5<>"",Yd<>"",Ydd<>""),AND(E5>=Ye,E5<=Yee,E5<>"",Ye<>"",Yee<>""),AND(E5>=Yf,E5<=Yff,E5<>"",Yf<>"",Yff<>""),AND(E5>=Yg,E5<=Ygg,E5<>"",Yg<>"",Ygg<>""),AND(E5>=Yh,E5<=Yhh,E5<>"",Yh<>"",Yhh<>""))

Thanks,

Michael.

[Ron Coderre]

I attached an edited version of your posted workbook.
I had to base the conditional formatting on helper-columns (to the right of the calendar ranges).
The helper columns determine if each date is included in one of the rental periods.

I hope that helps.

[vayana]

Ron,

Your example works great! thanks for your efforts on this I have been playing with the formula I posted earlier as well, and that does the trick as well so now I have 2 workable solutions. I'll compare the 2 to see which one I'll use in the end and post the result here for your review and for anyone else that is looking for a calendar with highlights.

I'll mark the thread solved after my comparison and review of the 2, and would like to thank everyone who has spent some time and effort on this!