Trouble with an IF Statement

[maic15]

I have two fields that contain dates: Start Date and End Date

I have another field (Week) that contains a string of dates.

I need to somehow write an IF Statement to compare the fields and return a value of 1 if True and 0 if False.

For example, If Start Date = 10/31/05 and End Date is 11/04/05 and Week = "10/31/05 - 11/04/05" , 1 should be returned. Since Week is text, there is no match.

So, my overall goal is IF Start Date and End Date are equal or between the Week string, then return 1, 0.
Thoughts?

 

[Von Pookie]

This seems to be working for me.

Where Start Date is in A2, End date is in B2, and the week string is in C2:
=IF(SEARCH(TEXT($A2:$B2,"mm/dd/yy"),$C2)>0,1,0)

This is an array formula--you have to press Ctrl+Shift+Enter after entering the formula for it to work correctly.

 

[maic15]

That solved half of my problem. If Start Date is 11/01/05 and End Date is 11/04/05, then I need a 1 to be returned.

If Start Date and End Date are in the range of week return 1, 0)

 

[Von Pookie]

Hmm. This is working for me:

Book1
	A	B	C	D
1	Start Date	End Date	Week
2	10/31/2005	11/4/2005	10/31/05 - 11/4/05	1
3	10/29/2005	11/4/2005	10/31/05 - 11/4/05	0
4	11/1/2005	11/4/2005	10/31/05 - 11/4/05	1
Sheet1

Formula in C2 is:
=IF(($A2>=DATEVALUE(LEFT($C2,FIND(" ",$C2)-1)))*($B2<=DATEVALUE(MID($C2,FIND("-",$C2)+2,LEN($C2)))),1,0)

then copied down the column.

 

[maic15]

One more condition that I forgot to apply.

If Start Date = 10/31/05 and End Date =11/16 and Week ="10/31/05 - 11/01/05", then return 1.

How do I add this condition?

 

[Von Pookie]

What exactly are the conditions, here?

Originally, you said: "If Start Date and End Date are equal or between the Week string, then return 1, 0."

But if Start Date = 10/31/05 and End Date =11/16 and Week ="10/31/05 - 11/01/05" ...the end date is not within the week date.

Basically, what I'm asking is what do you want? Does it not matter if the end date is past the week date?

Just a little confused, that's all...