Microsoft Excel 2003 (full product)

I have a list of dates when people are on holiday, e.g. Leave Date - Return Date, and I want to see if these dates encompas the date in a specific sell.

i.e. is 'A1' >=Leave Date and <=Return Date.

=IF(AND(B1>=$A$11,B1<=$B$11),"Yes","No")

This works fine when I am checking one set of dates, but is there a way of checking a range of dates in one go?

What do you mean by a "range of dates"? The example you gave above is prefectly clear. If you could provide as clear an example of what you are really trying to do, we might be able to help.

I have two lists of dates, in Column A I have Start Dates and Column B are Return Dates. I need the 'AND' statement to see if any date in Column A is <= B1 and if any date in column B is >= B1. Hope this is a bit clearer. Thanks in advance for your help.

re: if any date in Column A is <= B1 and if any date in column B is >= B1Are you sure that what you want?

This will give you that answer:

=IF(AND(B1>=MIN(A2:A4),B1<=MAX(B2:B4)),"Yes","No")

If you really want to compare the date in B1 to each

pairof dates in columns A & B, that's a different story.

Thanks for the quick reply but I dont think I was clear enough. I do need to comare if the Date in B1 is included to the pair of dates, (A5-B5, A6-B6 etc,

where B1 = 25/08/2010

and A5 = 01/08/2010 - B5 = 10/08/2010

A6 = 20/08/2010 - B6 = 27/08/2010We currently book our annual leave via a paper solution and I approve it for 5 staff and update a Wall Chart Calender of when each member of staff is on holiday. Due to changes at work I now have 30+ members of staff to do this for and would like to make it a little more automated so they can update their individual spredsheet and that will update the calender in Excel (I created a calender on a spreadsheet with the dates on Row 1 and Staffs names in Column A)

So the Calender will then look at all the spreadsheets and if the dates are inclusive of the time they have requested off they will have a 'Yes' in (I will change this to colours with 'Conditional Formatting' to make it easier to look at in a glance) this way I can see at a glance how many staff are off work on a perticular date.Thanks

I guess I should have posted the answer to the "different story". To compare the date in B1 to each pair of dates in columns A & B, try this:

=IF(SUMPRODUCT((B1>=($A$2:$A$4))*(B1<=($B$2:$B$4))),"Yes","No")

At first glance that looks like its sorted the problem. You have been a great help. Thankyou very much.

Craig

Glad to have been of assistance.

Ask Your Question

Weekly Poll