Date range problem

January 10, 2011 at 05:47:03
Specs: Windows XP
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/2010

If 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


See More: Date range problem

Report •

#1
January 10, 2011 at 06:11:46
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 between a 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 tried and we'll try to help further.

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


Report •

#2
January 10, 2011 at 07:59:52
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"


Report •

#3
January 10, 2011 at 08:58:17
I have a few of questions.

The dates in your From column (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 To dates 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 the From column, do we really need to be concerned with the To column?

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


Report •
Related Solutions


Ask Question