return TRUE if a date falls between two dates
 

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!

return TRUE if a date falls between two dates
 

One way:

=if(and(a5a2,a5<b2),true,false)

--
Regards,
Fred


"christine b" wrote in message
...
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!

return TRUE if a date falls between two dates
 

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


"christine b" wrote:

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!

return TRUE if a date falls between two dates
 

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

Mucly appreciated.
Christine


"Teethless mama" wrote:

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


"christine b" wrote:

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!

return TRUE if a date falls between two dates
 

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

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


"christine b" wrote:

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

Mucly appreciated.
Christine


"Teethless mama" wrote:

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


"christine b" wrote:

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!

return TRUE if a date falls between two dates
 

=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

return TRUE if a date falls between two dates
 

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)

"christine b" wrote in message
...
it works! you're a genious! :o)
For my humble education, can you tell me what "--" does in this formula?

Mucly appreciated.
Christine


"Teethless mama" wrote:

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


"christine b" wrote:

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!

return TRUE if a date falls between two dates
 

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.



"Rick Rothstein (MVP - VB)" wrote:

=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

return TRUE if a date falls between two dates
 

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

"Rick Rothstein (MVP - VB)" wrote:

=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