return TRUE if a date falls between two dates

[Guest]

I am looking for an "IF" statement to send back "T" or "F" (true/false), if
the date entered falls between two dates from a list, for all 5 rows...

The date entered, lets say in A5, of the "Sched" worksheet.

On the "Dates" worksheet, Column A are starting dates, Column B are end
dates. The cells are (group reference??) labelled "StartD" and "EndD".

A B Examples
11/01/07 28/01/07 Daycare
04/02/07 19/03/07 Community Service
28/03/07 09/05/07 Intern

Eventually, the true/false will be used for 3 other conditional formats and
calculations. I've tried VLOOKUP but can't figure my way around it. Any
help you can provide would be appreciated!

 

[Fred Smith]

One way:

=if(and(a5>a2,a5<b2),true,false)

 

[Guest]

=IF(SUMPRODUCT(--(StartD<=A5),--(EndD>=A5)),"T","F")

 

[Guest]

it works! you're a genious! )
For my humble education, can you tell me what "--" does in this formula?

Mucly appreciated.
Christine

 

[Guest]

Double unary "--" converts TRUE/FALE to 1/0 it is same as

=IF(SUMPRODUCT((StartD<=A5)*(EndD>=A5)),"T","F")

 

[Rick Rothstein \(MVP - VB\)]

=IF(SUMPRODUCT(--(StartD<=A5),--(EndD>=A5)),"T","F")

I wasn't entirely clear from Christine's posting which conditionals should
be considered True and which False; but assuming she liked the results your
formula yielded, I think this formula will work also....

=MID("FT",1+(StartD<=A5)*(EndD>=A5),1)

Rick

 

[Bob Phillips]

See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
explanation.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Rick,
not sure what this formula is doing, so I can't say what is wrong once I've
transfered to my sheet. it's flawed in that it does not find all dates -
maybe i've done something too. To be more clear: I enter a date in cell
A1. I want a formula to search the list and return true or false if I was
busy that day.

TeethlessMoma's formula works for this pupose, and I am also able to adapt
it to also flag false if the A1 date is a holiday or weekend.

here's what I did
=IF(OR(SUMPRODUCT(--(PLQStart<=C33),--(PLQEnd>=C33)),WEEKDAY(C33,2)>=6,NOT(ISNA(VLOOKUP(C33,Holidays,1,FALSE)))),"","o")

If you can find an easier way for the above, I'm all ears!!! So far, I'm
amazed with what I've done with the excel help file, and this posting.

 

[Guest]

just for some additional detail for what I am doing? I've used this
formula in each cell of a calandar so that tells me weekdays that I did not
work (excluding wknds and holidays).