Need Help with an IF Statement

[maic15]

Hi All,

I have an IF statement that is not meeting all of my conditions. Please see the following spreadsheet:

Book1
	A	B	C	D	E	F	G
1	Paramter Values
2	Start Date	End Date			Week	Condition True or False (1 Value is Returned)	If Statement Works
3	10/31/2005	11/4/2005			10/31/05 - 11/04/05	TRUE	X
4	11/1/2005	11/5/2005			10/31/05 - 11/04/05	TRUE	X
5	10/31/2005	11/18/2005			10/31/05 - 11/04/05	TRUE	X
6	11/4/2005	11/15/2005			10/31/05 - 11/04/05	FALSE
7
8	Condition Begin Used
9	"=IF(($E4>=DATEVALUE(LEFT($P3,FIND(" ",$P3)-1)))*($F4<=DATEVALUE(MID($P3,FIND("-",$P3)+2,LEN($P3)))),1,0)
Sheet1

I need to add another condition to capture make the statement that says False = True.
Thoughts?

 

[colbymack]

Maic15,
Can you provide the value that is stored in cell $P3, as referenced by your formula?

Also, can you indicate which cell the condition resides in (i.e. you have it displayed in A9, but I am guessing it is in either column F or G - can you also indicate which row.

Last, I assume you are trying to get a different value for the cell marked in red. Is that correct? If so, what value would you like to see?

Thanks,
Colbymack

 

[maic15]

P3 = E3 in my example

 

[maic15]

If False the value will be equal to 0

 

[colbymack]

Thanks for the added info. Just to clarify, in your example formula, you are comparing E4 to E3 (the first portion of the date), and comparing F4 to the second portion of E3.

This is confusing to me (forgive my ignorance) because the value in E4 is equal to the value in E3 in your example (they are both the date range of 10/31/05 - 11/04/05), which also means that P3 is equal to these. Additionally, F4 has a value of True or False being displayed, and not a date for you to use in comparison, as is being indicated in your sample formula:

"=IF(($E4>=DATEVALUE(LEFT($P3,FIND(" ",$P3)-1)))*($F4<=DATEVALUE(MID($P3,FIND("-",$P3)+2,LEN($P3)))),1,0)

I think I understand your formula, but it doesn't appear to line up with the data that you presented above it. Am I missing something?

Also, can you please indicate which cell this formula would reside in - is it the cell marked False, or the cell marked in red?

I think I can help you better once I have that info.

Thanks,
Colbymack