Search to data ranges and highlight matches

Hi Excel Guru's

I'm trying to create a leave calendar for staff. I would like to paste in the staff members sick and annual leave dates.

I would then like a conditional format that would highlight the leave dates onto the calendar, so i can visually track attendance and check for any sick leave patterns. My calendar looks like below for each month, and although it shows just the date in excel, i have formatted it so its actually the full date (ie instead of 1 it is 1/7/2013, but just displaying 1)

Jul-13
M T W T F S S
1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31

Whilst i can easily track and input the public holidays (on an individual basis) i'm looking to highlight the whole calendar area, say cells (c5:i10) as the search area and then have the leave data as a whole search area (at2:at150)

Above is part A of my question. To further complicate it, if it can even be done. The leave data i get from my system only has the start date of the leave, in another column it has the end date, and in another column it has days absent, if we can do it so it adds the days after (total leave days) to highlight in the calendar that would be amazing, if not, i'll just amend the data to have every date of the leave listed

Leave Type Start Date End Date Days Taken


Thanks guys

This illustrates how to apply CF based on a range of dates, viz: Start date to End date
Assuming the Start date is entered in AU3, End date in AV3
Select C5:I10 (ie your calendar area which is presumed to contain real dates)
Apply CF using the formula: =AND(C5>=$AU$3,C5<=$AV$3)
Format to suit > OK

Note that both Start date and End date should always be populated, if the leave is only for 1 day, then the same date should be entered into both Start / End date cells
---------------------------
Any worth? Wave it, hit the little star at the bottom left of my response

Hi Max,

Thank you for the response, whilst that works for one line of leave data, i will have a full year of information not just one line (AT2:AU50) for example. Then for another layer of complication at some point i'll have to consider the different types of leave.... :| but one problem at a time....

Leave Type Start Date End Date
SICK LEAVE 25/07/2013 31/07/2013
ANNUAL LEAVE 31/07/2013 31/07/2013
SICK LEAVE 15/08/2013 15/08/2013
ANNUAL LEAVE 23/08/2013 23/08/2013
SICK LEAVE 29/08/2013 29/08/2013
SICK LEAVE 30/08/2013 30/08/2013
SICK LEAVE 30/08/2013 30/08/2013
SICK LEAVE 17/09/2013 17/09/2013
SICK LEAVE 17/09/2013 17/09/2013

You could use something like this as the CF formula
=SUMPRODUCT((C5>=$F$5:$F$6)*(C5<=$G$5:$G$6))>0
where the Start dates and End Dates are in ranges F5:F6, G5:G6
---------------------------------
Any worth?

Thank you for the response Newdoverman!

However we are trying to develop a tool that highlights any patterns in leave. ie is the employee always taking a monday off work. Which is why i was looking at highlighting the dates off the data we obtain from the system

Thank you though