# How to use If and AND statements for date ranges

#### pauld333

##### New Member
I've set up an IF statement to check a cell between two dates using AND but for some reason it's not giving the correct result when the date entered is before the date that I am checking it against. It returns a zero result.

Any ideas where I'm going wrong in the formula?

=IF(AND(K\$43+0>\$C45+0,K\$43+0<\$D45),+\$H45*(DAYS360(K\$43,L\$43)),0)

Here's a screenshot:
https://dl.dropboxusercontent.com/u/23192837/Screen Shot 2015-05-18 at 22.14.57.png

Thanks
Paul

#### Scott Huish

##### MrExcel MVP
Why are you coercing the dates with +0, are they not actual dates?

Why shouldn't it be 0, that's what you asked it to do in your formula. Both parts of your AND statement must be TRUE to return TRUE.
In your example, K43 is not greater than C45, so it will skip to the FALSE part of your IF which returns 0.

Last edited:

#### MarcelBeug

##### Well-known Member
The screenshot shows that K43 (20/04/15) is not in the range C45-D45 (21/04/15 - 01/05/15), so the zero result seems correct to me.

#### pauld333

##### New Member
You're right having looked at it again.

But because K43 represents the week beginning date, I'm trying to get it to check if any of the dates in that week are between the dates in cells C45 to D45.

When I get that correct, I can use a series of IF statements to check the other dates to see if it falls between any of the other date ranges.

Is there a way of doing this?

Last edited:

#### Scott Huish

##### MrExcel MVP
You're doing it correct except that if you want it to include the date, you should use >= and <=

You shouldn't need the +0 at all.

#### pauld333

##### New Member
I've taken out the +0 as you suggested and it hasn't affected it.

I can't find a formula that will look to see if "any date" in the first range (ie any of the 7 days) is included in "any date" in the second range (ie within the date range of that stage of the project).

Would anybody have any ideas as to how this could be done?

#### pauld333

##### New Member
I've tried this formula but it won't work:

=IF(OR((C45>=K43<=D45),(C45>=L43<=D45)),+\$H45*(DAYS360(K\$43,L\$43)),0)

#### gior

##### New Member
Pls check such variant:

Code:
``=IF( AND( K\$43+7 >=\$C45, K\$43<\$D45), +\$H45*(DAYS360(K\$43,L\$43)),   0)``

#### pauld333

##### New Member
That seems to be working for all the dates I've tried. Thanks for that.

Except the fee that it works out is calculating the number of days from K43 to L43 - which will always be 7 being each week. However the finish date in D45 may be earlier or later than the date in L43. Even if I change the formula to ....+\$H45*(DAYS360(K\$43,D\$45) it won't be right.

Any ideas?

1,082,137
Messages
5,363,348
Members
400,729
Latest member
Lisa McConachy

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...