return TRUE if a date falls between two dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

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!
 
it works! you're a genious! :o)
For my humble education, can you tell me what "--" does in this formula?

Mucly appreciated.
Christine
 
Double unary "--" converts TRUE/FALE to 1/0 it is same as

=IF(SUMPRODUCT((StartD<=A5)*(EndD>=A5)),"T","F")
 
=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
 
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.
 
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).
 
Back
Top