| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Need true or false if a date falls between a date range | Excel Worksheet Functions | |||
| Search for 2 true arguments and return true or false | Excel Discussion (Misc queries) | |||
| Function to return True/False if all are validated as True by ISNU | Excel Worksheet Functions | |||
| Return a specified date when it falls within a range.... | Excel Discussion (Misc queries) | |||
| How to find if a date falls between 2 dates | Excel Worksheet Functions | |||
Linear Mode
