formula to determine whether a date falls between two dates

[lolan7]

Hi. I hope there's an answer to this problem:
N column is "Start Date",
O column is "End Date",
I have a named range (not sure whether this is necessary) "holidays" in
U3:U9.

I need to find out if any of the "holidays" dates fall within my Start Dates
and my End Dates, which vary from row to row.

thanks for being brilliant.

 

[lolan7]

sorry that I posted this question 3x; I kept getting a failure to post message.

 

[T. Valko]

Ok, so what kind of result do you want? A count? A "Yes" or "No"?
True/False?

 

[lolan7]

The result will be in another cell.
Ultimately I will need to know which of the holidays falls within the
Start/End dates on any given line because I will need to know what day of the
week (mon, tue, etc., --which are listed in V3:V9 next to the holidays using
a TEXT formula) and then use this information to subtract those days from a
total number of sessions/hours which have been calculated in different
columns.

The sessions don't happen every day; some are on Mondays, some on Tuesdays &
Thursdays, etc.

 

[Fred Smith]

You still didn't answer Biff's question. So see if this points you in the
right direction.

=if(and(u3>n2,u3<o2),"between dates","outside date range")

Regards,
Fred.

 

[Max]

Perhaps check up NETWORKDAYS in Excel's help
It might be just what you need, as per your original post

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---

 

[T. Valko]

Let's try this...

..............N.................O
3....1/1/2009.....1/31/2009

V3 = 1/1/2009
V4 = 1/15/2009
V5 = 2/21/2009
V6 = 5/5/2009
V7 = 5/31/2009
V8 = 7/4/2009
V9 = 9/5/2009

What result do you expect and where do you expect it to appear?

 

[lolan7]

This helps a LOT! It at least provides me a way to figure it out,
but....perhaps if I am a bit more clear in my needs, you have a calculation
that does ALL the work:
I am trying to determine whether to subtract a holiday from a schedule of
classes. There is a definite start date & a definite end date for each class
(which varies from class to class) - there is one class per row.
Some classes are only on Thursdays; some are on Mondays & Wednesday, etc.
which are indicated in columns H thru K, e.g., M _ W _ (or) _ T _ R.

If any of my holidays fall on a scheduled day, I need to subtract them from
my final count of class sessions, summed up in column P with this formula:
=IF(COUNT(N5:O5)<>2,"",SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(N5&":"&O5)),2)=MATCH(H5:K5,{"M","T","W","R","F","SA","SU",0},0))))

I will want to approach this by showing, in a separate column, not yet
created for each line how many of the scheduled days are holidays,, then
subtract that from column P. I transposed my column of holidays (U3
through U10) to column headings (X2 through AD2) and used your formula,
replacing the "phrases" with 1 (between) or 0 (outside). However, doing so,
I still have to determine whether it corresponds to an appropriate day of the
week.

For this semester I have eight holidays, three Mon., one Tues., one Wed.,
one Thurs., and one Fri.

(you can probably guess that I did not come up with the above formula. I
got it from one of your brilliant coterie of MVPs)

thanks for being here!