Hi ALL, Can anyone please help. I have to write a formula in excel to look for a date range. Here is an example of what I have and what results I need.

Absence from to

Holiday 01/03/2010 15/03/2010

Sickness 01/04/2010 04/04/2010If I search for a date. ie: 04/03/2010 I would like the result to show : "Holiday"

If I search for a date. ie: 03/04/2010 I would like the result to show : "Sickess"I would apreciate any help in this regard.

Many thanks,

Adam

Since this sounds like a homework assignment, we won't give you the final solution. We will, however, try to point you in the right direction.

Obviously you need an IF function, so the question is: What are the conditions that you need to check for?

Since you need to check for a date that is

betweena start date and an end date, it would be nice if Excel had a "between" function, but alas, it doesn't.They do however, have an AND function and the ability to check check to see if one value is "less than" or "greater than" another.

Look up the AND function in Excel Help and see if you figure out how to use it for this situation.

If you have trouble figuring it out, come on back

with what you've triedand we'll try to help further.

Click Here Before Posting Data or VBA Code --->How To Post Data or Code.

Many thanks for the heads up.

This is what I came up with so far. I know it's missing something cause it's not working yet, but here is a try:=IF(MATCH(D1,B:B,0)),D1-VLOOKUP(D1,B:B,1,1),0)

assuming D1 is the date 'm querying "ie: 04/03/2010"

I have a few of questions. The dates in your

Fromcolumn (which I assume is Column B) appear to be in ascending order. Will this always be the case or can the sheet be set up this way if required?The date ranges do not seem to overlap. Will this always be the case?

Do the

Todates really matter? In other words, if the date in D1 is X, and X is always equal to or greater than one of the dates in theFromcolumn, do we really need to be concerned with theTocolumn?

Click Here Before Posting Data or VBA Code --->How To Post Data or Code.

Ask Your Question

Weekly Poll