formula to determine whether a date falls between two dates
 

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.

formula to determine whether a date falls between two dates
 

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

"lolan7" wrote:

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.

formula to determine whether a date falls between two dates
 

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

--
Biff
Microsoft Excel MVP


"lolan7" wrote in message
...
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.

formula to determine whether a date falls between two dates
 

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.


"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"lolan7" wrote in message
...
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.

formula to determine whether a date falls between two dates
 

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

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

Regards,
Fred.

"lolan7" wrote in message
...
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 O22
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.


"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"lolan7" wrote in message
...
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.

formula to determine whether a date falls between two dates
 

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
---

formula to determine whether a date falls between two dates
 

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?

--
Biff
Microsoft Excel MVP


"lolan7" wrote in message
...
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.


"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"lolan7" wrote in message
...
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.

formula to determine whether a date falls between two dates
 

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!

"Fred Smith" wrote:

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

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

Regards,
Fred.

"lolan7" wrote in message
...
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 O22
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.


"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"lolan7" wrote in message
...
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.