How to use If and AND statements for date ranges

[pauld333]

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]

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.

 

[MarcelBeug]

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]

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?

 

[Scott Huish]

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]

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]

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]

Pls check such variant:

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

 

[pauld333]

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?