How to use If and AND statements for date ranges

pauld333

New Member
Joined
Apr 18, 2015
Messages
17
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
Joined
Mar 17, 2004
Messages
19,886
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
Joined
Apr 25, 2014
Messages
1,811
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
Joined
Apr 18, 2015
Messages
17
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
Joined
Mar 17, 2004
Messages
19,886
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
Joined
Apr 18, 2015
Messages
17
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
Joined
Apr 18, 2015
Messages
17
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
Joined
Aug 8, 2011
Messages
32
Pls check such variant:

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

pauld333

New Member
Joined
Apr 18, 2015
Messages
17
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?


 

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top