# Trouble with an IF Statement

#### maic15

##### Active Member
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?

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

#### Von Pookie

##### MrExcel MVP
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

##### Active Member
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

##### MrExcel MVP
Hmm. This is working for me:
Book1
ABCD
1Start DateEnd DateWeek
210/31/200511/4/200510/31/05 - 11/4/051
310/29/200511/4/200510/31/05 - 11/4/050
411/1/200511/4/200510/31/05 - 11/4/051
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

##### Active Member
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

##### MrExcel MVP
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...

Replies
2
Views
247
Replies
0
Views
139
Replies
3
Views
251
Replies
5
Views
337
Replies
3
Views
318

1,195,948
Messages
6,012,472
Members
441,701
Latest member
vnkendijs

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back